Adding numbers from a loop

[htmanning]

Sorry for the simple question but I'm having a brain-fart here. I'm searching records in a database to find a revenue number. It works fine if there is only one record, but if there is more than one record returned, how do I added the numbers up?

$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 = 60;
 } elsif ($total eq "10") {
 $revenue = 100;
 } 
 
 }
$sth->finish();

So if the first record returns a $total=3 and the second record returns a $total= 6, how do I keep track and add them together so the total revenue is accurate.? I'm going in circles.

[1nickt]

Maybe use your database's sum() function?

Or

my $sum = 0;
for my $record ( @loop ) {
    $sum += $record->{val};
}

Hope this helps!

[htmanning]

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.

[stevieb]

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
        };

[syphilis]

 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

[1nickt]

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!

[tweetiepooh]

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

[BillKSmith]

There is nothing wrong with your code. All you really have to add total_revenue processing is to declare $total_revenue outside the loop and add to it inside the loop. However, you can simplify the logic by using stevieb's hash suggestion.

>type htmanning2.pl
use strict;
use warnings;

my @hash_rows = (
    {total => 3, other_col => 'fie'},
    {total => 6, other_col => 'fum'},
    {total => 5, other_col => 'oop'},
);

my %revenues = (
     3 => 30,
     6 => 48,
    10 => 50,
);

my $total_revenue;
foreach my $pointer (@hash_rows) {  # simulate fetchrow_hashref for po
+st
    my $total = $pointer->{total};
    next if (!defined $total or !exists $revenues{$total});
    my $revenue = $revenues{$total};
    $total_revenue += $revenue;
    print "revenue: $revenue\ntotal_revenue $total_revenue\n\n";
}

>perl htmanning2.pl
revenue: 30
total_revenue 30

revenue: 48
total_revenue 78


>