how to sort this sum

[tomrock_shi]

i have this list, about some company payment , in the list, some company pays two times in a particular day. i need to write a perl program to add up the company's total payment for that particular day such as

eid date amount
00101 2009-12-21 3009
00101 2009-12-21 165
00101 2009-12-29 235.5
00201 2009-12-17 2583
00201 2009-12-17 394.5
00301 2009-12-29 3738
00401 2009-12-17 2244
00501 2009-12-24 2851.5
00601 2009-12-17 19450.5
00701 2009-12-17 15240
00801 2009-12-17 11277
00901 2009-12-21 16056

where eid represent the company

combine the amount of the first 2 line as the transcation are in same date and print out like this:

eid date amount
00101 2009-12-21 3174
00101 2009-12-29 235.5
00201 2009-12-17 2977.5
00301 2009-12-29 3738
....
....
....

[d5e5]

I recommend installing the DBI and SQLite modules for perl and inserting your data into a database table as follows:

#!/usr/bin/perl
use strict;
use warnings;

use DBI;

# Connection to DB file created before
my $dbh = DBI->connect("dbi:SQLite:dbname=demo.db","","");

$dbh->do("CREATE TABLE payments (eid,date,amount)");
 
my $sth = $dbh->prepare("insert into `payments` (eid,date,amount) values (?, ?, ?)")
             or die "prepare failed: " . $dbh->errstr();  

while (<DATA>){
    chomp;
    my ($eid, $date, $amount) = split;
    $sth->execute($eid, $date, $amount) or die "execute failed: " . $sth->errstr(); 
}

# Close connection
$sth->finish();
undef($dbh);

__DATA__
00101 2009-12-21 3009
00101 2009-12-21 165
00101 2009-12-29 235.5
00201 2009-12-17 2583
00201 2009-12-17 394.5
00301 2009-12-29 3738
00401 2009-12-17 2244
00501 2009-12-24 2851.5
00601 2009-12-17 19450.5
00701 2009-12-17 15240
00801 2009-12-17 11277
00901 2009-12-21 16056

After doing this, writing queries to select and summarise the data in various ways will be straightforward, once you learn a few simple SQL statements.

[d5e5]

If you don't want to insert your data into a database first, you could read them into a hash, adding the amounts as you do so.

#!/usr/bin/perl
use strict;
use warnings;

my %payments;
while (<DATA>){
    chomp;
    my ($eid, $date, $amount) = split;
    next if m/^\D/; #Skip header line (starts with non-digit character)
    my $key = $eid . ' ' . $date;#Build key for groups to sum
    $payments{$key} = 0 unless exists($payments{$key});
    $payments{$key} += $amount;
}

print "eid   date\t\tamount\n";
foreach my $key(sort keys %payments){
    print "$key\t$payments{$key}\n";
}

__DATA__
eid date amount
00101 2009-12-21 3009
00101 2009-12-21 165
00101 2009-12-29 235.5
00201 2009-12-17 2583
00201 2009-12-17 394.5
00301 2009-12-29 3738
00401 2009-12-17 2244
00501 2009-12-24 2851.5
00601 2009-12-17 19450.5
00701 2009-12-17 15240
00801 2009-12-17 11277
00901 2009-12-21 16056

This gives the following output:

eid   date		amount
00101 2009-12-21	3174
00101 2009-12-29	235.5
00201 2009-12-17	2977.5
00301 2009-12-29	3738
00401 2009-12-17	2244
00501 2009-12-24	2851.5
00601 2009-12-17	19450.5
00701 2009-12-17	15240
00801 2009-12-17	11277
00901 2009-12-21	16056

[tomrock_shi]

If you don't want to insert your data into a database first, you could read them into a hash, adding the amounts as you do so.

#!/usr/bin/perl
use strict;
use warnings;

my %payments;
while (<DATA>){
    chomp;
    my ($eid, $date, $amount) = split;
    next if m/^\D/; #Skip header line (starts with non-digit character)
    my $key = $eid . ' ' . $date;#Build key for groups to sum
    $payments{$key} = 0 unless exists($payments{$key});
    $payments{$key} += $amount;
}

print "eid   date\t\tamount\n";
foreach my $key(sort keys %payments){
    print "$key\t$payments{$key}\n";
}

__DATA__
eid date amount
00101 2009-12-21 3009
00101 2009-12-21 165
00101 2009-12-29 235.5
00201 2009-12-17 2583
00201 2009-12-17 394.5
00301 2009-12-29 3738
00401 2009-12-17 2244
00501 2009-12-24 2851.5
00601 2009-12-17 19450.5
00701 2009-12-17 15240
00801 2009-12-17 11277
00901 2009-12-21 16056

This gives the following output:

eid   date		amount
00101 2009-12-21	3174
00101 2009-12-29	235.5
00201 2009-12-17	2977.5
00301 2009-12-29	3738
00401 2009-12-17	2244
00501 2009-12-24	2851.5
00601 2009-12-17	19450.5
00701 2009-12-17	15240
00801 2009-12-17	11277
00901 2009-12-21	16056

thank you, that solved my problem. i have another problem.
lets say i randomly pick some company, i add up their total amount, i get 17259. but you don't know which companies i choose to get this number. how do you find out? is that possible to use combination to get it?

[d5e5]

thank you, that solved my problem. i have another problem. lets say i randomly pick some company, i add up their total amount, i get 17259. but you don't know which companies i choose to get this number. how do you find out? is that possible to use combination to get it?

For sake of clarity and searching the forum topics, when you have another question you should start another thread. However, I can tell you I can't think of any way to know which companies' amounts you chose to add up to 17259 except to ask you.