Sum Fields in Array

[drodinthe559]

Monks, is it possible to sum value using an array for certain clients? This would be similar to SQL in doing a group by.I want to sum the debit and credit fields. Below is what my array looks like.

        push @fields, {type => $type,
                       batch_number => $batch_num,
                       effective_entry => $effent_date,
                       entry_description => $entry_desc,
                       date_created => $date_crt,
                       load_number => $load_num,
                       fooddebits => $debits,
                       foodcredits => $credits};

[Corion]

The Perl equivalent to a GROUP BY clause in SQL is a hash. You use the parameter of the GROUP BY ... clause as the keys of the hash, and aggregate the value(s) of the rows in the values of the hash.

A rough Perl implementation for a single aggregate (sum) could look like this:

use Scalar::MoreUtils qw(zip); # I'm lazy

sub sum_group_by {
    my ($key_columns, $aggregate_columns, $rows) = @_;

    my %result;

    for my $input_row (@$rows) {
        # Create the synthetic key, assuming that no value will contai
+n a \0:
        my @key_values = @{ $input_row }{ @$key_columns };
        my $key = join "\0", @key_values;

        # If this is a new key, create an new result row for it that c
+ontains the key values:
        $result{ $key } ||= { zip @$key_columns, @key_values };

        for my $aggregate (@$aggregate_columns) {
            $result{ $key }->{ $aggregate } += $input_row->{ $aggregat
+e };
        };
    };

    # Return the result as new rows, ordered asciibetically (because I
+'m lazy) by their key:
    map { $result{ $_ } } sort keys %result;
};

If you can manage that all your operations return lists sorted by their keys, or can allow an intermediate step that (pre)sorts all inputs according to their keys, you can make the aggregation much simpler by aggregating into a single value and outputting a new row whenever the key (after which you sorted) changes. I have seen no paper or anaylis that measures which is better, pre-sorting the input and then pipelined aggregation or aggregation into a hash and then (potentially sorted) output.

Update: Also consider just using SQL through either DBD::SQLite or DBD::AnyData as a way to manipulate data in a way that is very efficient to write for you, and likely better tested than a hand-rolled aggregation.

[drodinthe559]

Great! Thank you. You've given me something to work with.

[toolic]

Loop through your array, then reference the debit and credit keys, then add them:

use strict;
use warnings;

my @fields;
push @fields, {fooddebits => 55, foodcredits => 22};
push @fields, {fooddebits => 12, foodcredits => 10};

for (@fields) {
    print $_->{fooddebits} + $_->{foodcredits}, "\n";
}

__END__
77
22

[linuxer]

I admit, I have to reactivate my SQL knowledge .... Maybe List::Util can help you:

use List::Util qw( sum );
my $sum_debit = sum map { $_->{fooddebits} } @fields;