Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Re^2: Adding numbers from a loop

by htmanning (Pilgrim)
on Aug 06, 2019 at 22:54 UTC ( #11104067=note: print w/replies, xml ) Need Help??


in reply to Re: Adding numbers from a loop
in thread Adding numbers from a loop

Sorry, my example was bad because the revenue changes based on the number in the $total column. So this is better:
$SQL = "select * from datbase where (TO_DAYS(NOW())-TO_DAYS(dateadded) +<1)"; # look for records less than 1 day old &Do_SQL; while ($pointer = $sth->fetchrow_hashref) { $total = $pointer->{'total'}; if ($total eq "3") { $revenue = 30; } elsif ($total eq "6") { $revenue = 48; } elsif ($total eq "10") { $revenue = 50; } } $sth->finish();
I don't need to simply add up the $total fields (i.e. 3 + 6 = 9). I need to know that one record had $total = 3 so $revenue = 30 and the next record is $total = 6 so $revenue = 48. $total_revenue = 78.

I hope that makes it clearer. I don't think the SUM function can do that for me OR I just don't know how to do it. I can easily grab the SUM of the $total fields, but that's not what I need.

Appreciate the help.

Replies are listed 'Best First'.
Re^3: Adding numbers from a loop
by stevieb (Canon) on Aug 07, 2019 at 00:03 UTC

    Are you looking to gather aggregates based on whether a total is something?

    For instance, you appear to be looping over db rows, so if 'total' column is '3', you want to sum those up individually?

    If so, use a hash:

    my %h; ...; # get info from db $h{$total} += $revenue;

    Example:

    use warnings; use strict; use Data::Dumper; my %h; while (<DATA>){ my ($total, $revenue) = split; $h{$total} += $revenue; } print Dumper \%h; __DATA__ 3 555 4 962 3 1 3 1064 5 19 17 8 45 -1

    Output:

    $VAR1 = { '5' => 19, '3' => 1620, '45' => -1, '4' => 962, '17' => 8 };
Re^3: Adding numbers from a loop
by syphilis (Bishop) on Aug 07, 2019 at 03:37 UTC
    if ($total eq "3") { $revenue = 30; } elsif ($total eq "6") { $revenue = 48; } elsif ($total eq "10") { $revenue = 50; }

    Is it the case that what you're really after is:
    if ($total eq "3") { $revenue += 30; } if ($total eq "6") { $revenue += 48; } if ($total eq "10") { $revenue += 50; }
    Hmmm .... on some reflection, replacing "elsif" with "if" (as I have done), doesn't really achieve anything.
    And replacing "+" with "+=" does, of course, change things only if the code is run more than once.
    I now think that maybe I don't understand the problem at all ....

    Cheers,
    Rob
Re^3: Adding numbers from a loop
by 1nickt (Abbot) on Aug 07, 2019 at 02:25 UTC

    You have not said whether the other number is in the database or elsewhere in your code.

    I assume not in the DB, or you'd just multiply the values in the columns: $sum += ($record->{total} * $record->{price});.

    If from another source, and you need to look it up, say from a price sheet based on quantity ordered, make yourself a lookup table:

    use strict; use warnings; use feature 'state', 'say'; use Test::More; use JSON; sub calc_rev { state %price_by_qty = ( 1 => 15, 3 => 10, 6 => 8, 10 => 5, ); my $qty = shift; return 0 if ! $qty; for my $level ( sort { $b <=> $a } keys %price_by_qty ) { return $qty * $price_by_qty{ $level } if $qty >= $level; } } # # mock getting an aref of rows for this test file my $json = do { local $/; <DATA> }; my $rows = decode_json( $json ); # my $revenue = 0; for my $row ( @{ $rows } ) { $revenue += calc_rev($row->{total}); } is( $revenue, 123, 'Revenue is 123. Mwahahahaa!'); done_testing; __DATA__ [ {"total":3}, {"total":6}, {"total":1}, {"total":2} ]
    Output:
    $ perl foo.pl ok 1 - Revenue is 123. Mwahahahaa! 1..1

    Hope this helps!


    The way forward always starts with a minimal test.
Re^3: Adding numbers from a loop
by tweetiepooh (Hermit) on Aug 07, 2019 at 11:58 UTC

    Does your database have a lookup type function that will translate for you then you can use SUM. This is for Oracle and may not be the best alternative even there.

    select sum(decode(total,3,30,6,48,10,50,0)) from table

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://11104067]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (6)
As of 2019-12-13 06:25 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?