Page 1 of 1

MySQL Column SUMs into different output variables?

Posted: Thu Jan 31, 2008 5:28 pm
by batfastad
Hi everyone

I hope I can explain this correctly.
Basically I have a database with 3 fields... an ID auto-increment, 'currency' which is a 3 letter varchar, and 'amount' which is a float.

Each record has a 3 letter currency code, and an amount.
At the moment there are only 3 different currency codes used.
GBP, EUR and USD

At the moment if I want to get a total of all the currencies, I basically loop through all the records in PHP and add them all up into 3 separate PHP vars.
That's fine at the moment... but when we've got tens of thousands of records in there, I'd like to find something a bit more efficient.

Usually I would just do this... SELECT 'total' AS SUM('amount')
But I would like to get the SUMS for each currency, not all the amounts added up.

Is there a way to return the totals of all 3 currencies, but within one query?

The only other way I can think is to just have 3 queries, one for each currency.
Or leave it as it is, looping through all the records in PHP. But sometimes I guess it's more efficient to get MySQL to work on things like that rather than PHP.
I'm not sure at which point in terms of record numbers this will start being an issue though. I'm guessing into the 100k number of records then it makes no sense to loop in PHP any more.

Any other ideas / suggestions?
Or have I just got everyone confused about what I'm trying to do?

Thanks, Ben

Re: MySQL Column SUMs into different output variables?

Posted: Thu Jan 31, 2008 5:47 pm
by Christopher
Look into "GROUP BY currency"

Re: MySQL Column SUMs into different output variables?

Posted: Fri Feb 01, 2008 6:09 am
by batfastad
Aha! Perfect. Wow that's really useful
Thanks so much for your help ;)