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

hi PerlMonks

I think this question may be more related to MySQL than Perl, but I think there maybe a quick way to do it using Perl? (Apologies if this is mysql related)

What I have is a mysql table with products and one of the columns in the table is 'fee' - I would like to find out the total price by adding up all the fee rows.

I know I could use a while loop to accomplish this, but there maybe a few thousand products soon so I don't know if going through one by one is the best solution in terms of speed.

Any thoughts/advice very appreciated. Best Regards

Replies are listed 'Best First'.
Re: Perl/MySQL ?
by Kanji (Parson) on May 13, 2002 at 01:05 UTC
    Any thoughts/advice very appreciated.

    Don't optimize prematurely. :-)

    Seriously, summing ~10K rows from a database using a while/+= combo takes fractions of a second on my 1.8GHz ... plenty 'quick' for me.

    As for a MySQL solution, see SUM().

        --k.


[reply]
Re: Perl/MySQL ?
by grep (Monsignor) on May 13, 2002 at 01:17 UTC
    Kanji is absolutly right you are looking for the SUM() function. You should also learn about all your other aggregate SQL functions.

    grep
    Unix - where you can throw the manual on the keyboard and get a command
[reply]
Simple query
by Olecram (Initiate) on May 13, 2002 at 10:32 UTC
    You need something like

    SELECT category, SUM(fee) FROM tblproducts WHERE category like 'Phorno
+';

    [download]
    That should be it. Since SUM is a grouping func, you need to apply all grouping rules to the selects you plan. Hence be sure the SUM set returns an unique index (for example the CATEGORY here, for which is made a subset).
    Without the WHERE, I'd have as much rows in the resulting recordset as there are CATEGORIES in the table.

    Hope I made myself clear. :)
[reply]
[d/l]
      Your SQL is incorrect since there is no "group by" expression used in conjuction with the aggregate function: 
      SELECT category,
       SUM(fee)
FROM   tblproducts
/* optional where clause */
GROUP BY category

      [download]
      would be correct.

      rdfield

[reply]
[d/l]

Back to Seekers of Perl Wisdom