SQL Database Help!!

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

How could I use perl to query a database ad and print the results of the columns that has numbers to be added. And print the results of all the rows the have different names on them like:

First Column   - Second Value - Third Value - Four value

  Item A                 345.00          345         34.44
  Item A                  34.00           65         76.43
  Item B                1000.00          345          1.50

How using perl to fetch the database I could have the print out result like:

Item A  379.00    410      11087
Item B  1000.00   345       1.50

Thanks for the help!!!!

Comment on SQL Database Help!!

Replies are listed 'Best First'.
Re: SQL Database Help!! by runrig (Abbot) on Apr 18, 2003 at 19:44 UTC
If you don't need to look at the individual rows, then just use sql to select the aggregates: `select field1, sum(field2), sum(field3), sum(field4) from table group by field1 order by field1` [download]	[reply] [d/l]
Re: SQL Database Help!! by chromatic (Archbishop) on Apr 18, 2003 at 19:40 UTC
Loop through the result set and add them to a data structure indexed on the contents of the first column. `my %items; while (my ($item, $cost, $number, $value) = $sth->fetchrow_array()) { $items{ $item }{ cost } += $cost; $items{ $item }{ number } += $number; $items{ $item }{ value } += $value; }` [download] Then just loop through the data structure and print appropriately. You could also order the rows by the first column, keep a running tally in `$cost`, `$number`, and `$value`, and print a line when the first column changes.	[reply] [d/l] [select]