SELECT SUM(IF(cur = 'EUR',amount,0)) as "Amount, euro", SUM(IF(cur = 'FIM',amount / 5.94573,0)) as "Amount, fim -> euro", SUM(IF(cur = 'SEK',amount / 9.294,0)),0)) as "Amount, sek -> euro" FROM table1;
I get the total amount spent -- converted into one currency, euro -- for the respective currencies. But it would also be nice to have a grand total of money spent. That is, now that I know how to convert the sums by exchange rate, is there a way for me to sum it all up; i.e. "Amount, ,euro" + "Amount, fim -> euro" + "Amount, sek -> euro" = grand total spent, converted into euro? (That's what I was trying to do with the nested sum. :) )
Kristian
Yoed Anis wrote:
Hmm, I'm not exactly sure that I understand what you are trying to do now.
But why do a nested sum? Wouldn't one sum be enough? After all you are already taking into consideration the currency change by the division factor.
Yoed
-----Original Message-----
From: Kristian Niemi [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 28, 2004 1:23 AM
To: Yoed anis
Subject: Re: SUM same field twice in one SELECT?
Thanks!
That enabled me to convert the different currencies into one specific currency.
Now, if I could bother you a bit more, and ask how I would do to get a grand total of, say, SEK and EUR now that they're converted into the same currency? How to sum the sums, so to speak.
I tried a nested sum:
SELECT SUM(IF(cur = 'EUR',amount + SUM(IF(cur = 'FIM',amount / 5.94573,0)) + SUM(IF(cur = 'SEK',amount / 9.294,0)),0)) FROM table1;
But that only got me a "Invalid use of group function" error-message.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
