$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();
[download]

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.

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;
[download]

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
[download]

Output:

$VAR1 = {
          '5' => 19,
          '3' => 1620,
          '45' => -1,
          '4' => 962,
          '17' => 8
        };
[download]

 if ($total eq "3") {
 $revenue = 30;
 } elsif ($total eq "6") {
 $revenue = 48;
 } elsif ($total eq "10") {
 $revenue = 50;
 }
[download]


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;
 }
[download]

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

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}
]
[download]

Output:

$ perl foo.pl

ok 1 - Revenue is 123. Mwahahahaa!
1..1
[download]

Hope this helps!



The way forward always starts with a minimal test.

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
[download]