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();

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

    [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]
[reply]
[d/l]
[select]
Re^3: Adding numbers from a loop
by syphilis (Archbishop) 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;
 }

    [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
[reply]
[d/l]
[select]
Re^3: Adding numbers from a loop
by 1nickt (Canon) 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}
]

    [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.
[reply]
[d/l]
[select]
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

    [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom