MySQL Column SUMs into different output variables?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

MySQL Column SUMs into different output variables?

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: MySQL Column SUMs into different output variables?

Post by Christopher »

Look into "GROUP BY currency"
(#10850)
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL Column SUMs into different output variables?

Post by batfastad »

Aha! Perfect. Wow that's really useful
Thanks so much for your help ;)
Post Reply