Summing sales across different currencies in SQL

Question

This is a follow-up to a previous questions I asked: Using a GROUP BY statement to sum rows. I have a table with sales of different currencies, and I am using a GROUP BY statement to sum up the numbers per title.

mysql> SELECT
       title, 
       SUM(us_earnings_usd) AS usd,
       SUM(cad_earnings_cad) AS cad,
       SUM(uk_earnings_gbp) AS gbp,
       SUM(swedish_earnings_skk) AS skk
       FROM raw_financials 
       WHERE date="2012-12-01"
       GROUP BY title

+--------+-----------------+------------------+------------------+------------------+
| title  | us_earnings_usd | cad_earnings_cad |swedish_earnings_ |  uk_earnings_gbp |
+--------+-----------------+------------------+------------------+------------------+
| Gamers |          7.7500 |           4.0000 |           1.0000 |           2.0000 |
+--------+-----------------+------------------+------------------+------------------+

Finally, I need to sum up the fields to get the total sales in USD. For this, I have an additional table called exchange_rates. This is how it can be queried:

mysql> SELECT currency, conversion_to_usd FROM exchange_rates WHERE date="2011-12-01";

+----------+-------------------+
| currency | conversion_to_usd |
+----------+-------------------+
| AUD      |           0.98542 |
| CAD      |           0.95940 |
| CHF      |           1.05235 |
| DKK      |           0.17372 |
| EUR      |           1.29400 |
| GBP      |           1.54223 |
| NOK      |           0.16579 |
| NZD      |           0.74442 |
| SEK      |           0.14190 |
| USD      |           1.00000 |
+----------+-------------------+

How would I combine these using SQL to get:

total_earnings_in_usd = 7.75 (earnings in usd) *1.00 (conversion from usd to usd) 
                        + 4.00 (earnings in cad) *0.95 (conversion from cad to usd) 
                        + 1.00 (earnings in sek) *0.14 (conversion from sek to usd)
                        + 2.00 (earnings in gbp) *1.54 (conversion from gbp to usd) 
                      = $14.77 USD

Update: I have updated the SQL in the question.

Answer 1

I tested the SQL(T-SQL in SQL Server) below. It returns 11.5876 as grand_total. Is this okay for you?

create table #raw_financials (
    title varchar(20)
    ,partner_share_currency varchar(20)
    ,us_earnings_usd decimal(15, 5)
    ,cad_earnings_cad decimal(15, 5)
    ,date datetime
)
insert into #raw_financials values ('Gamers', 'USD', 3.25, 0, '2012-12-01')
insert into #raw_financials values ('Gamers', 'CAD', 0, 4, '2012-12-01')
insert into #raw_financials values ('Gamers', 'USD', 4.5, 0, '2012-12-01')

create table #exchange_rates (
    currency varchar(20)
    ,conversion_to_usd decimal(15, 5)
    ,date datetime
)
insert into #exchange_rates values ('CAD', 0.95940, '2012-12-01')
insert into #exchange_rates values ('USD', 1, '2012-12-01')

SELECT
rf.title,
--partner_share_currency,
SUM((rf.us_earnings_usd + rf.cad_earnings_cad) * er.conversion_to_usd) AS grand_total
FROM #raw_financials rf
INNER JOIN #exchange_rates er
ON er.currency = rf.partner_share_currency
AND er.date = rf.date
WHERE rf.title LIKE '%Gamers%' AND rf.date='2012-12-01'
GROUP BY rf.title

But I have never used MySQL, so the result might be different.

Update: Here is another query for GBP and SEK. It returns 14.81396 in my T-SQL:

create table #raw_financials (
    title varchar(20)
    ,partner_share_currency varchar(20)
    ,us_earnings_usd decimal(15, 5)
    ,cad_earnings_cad decimal(15, 5)
    ,uk_earnings_gbp decimal(15, 5)
    ,swedish_earnings_skk decimal(15, 5)
    ,date datetime
)
insert into #raw_financials values ('Gamers', 'USD', 3.25, 0, 0, 0, '2012-12-01')
insert into #raw_financials values ('Gamers', 'CAD', 0, 4, 0, 0, '2012-12-01')
insert into #raw_financials values ('Gamers', 'USD', 4.5, 0, 0, 0, '2012-12-01')
insert into #raw_financials values ('Gamers', 'GBP', 0, 0, 2, 0, '2012-12-01')
insert into #raw_financials values ('Gamers', 'SEK', 0, 0, 0, 1, '2012-12-01')

create table #exchange_rates (
    currency varchar(20)
    ,conversion_to_usd decimal(15, 5)
    ,date datetime
)
insert into #exchange_rates values ('CAD', 0.95940, '2012-12-01')
insert into #exchange_rates values ('USD', 1, '2012-12-01')
insert into #exchange_rates values ('GBP', 1.54223, '2012-12-01')
insert into #exchange_rates values ('SEK', 0.14190, '2012-12-01')

SELECT
rf.title,
SUM((rf.us_earnings_usd + 
     rf.cad_earnings_cad + 
     rf.uk_earnings_gbp + 
     rf.swedish_earnings_skk
    ) * er.conversion_to_usd) AS grand_total
FROM #raw_financials rf
INNER JOIN #exchange_rates er
ON er.currency = rf.partner_share_currency
AND er.date = rf.date
WHERE rf.title LIKE '%Gamers%' AND rf.date='2012-12-01'
GROUP BY rf.title

Answer 2

UPDATED FOLLOWING COMMENT

SELECT  title, 
        partner_share_currency,
        SUM(us_earnings_usd) AS usd,
        SUM(cad_earnings_cad) AS cad,
        SUM(us_earnings_usd) + SUM(cad_earnings_cad*CAD) +
        SUM(uk_earnings_gbp*GBP) + SUM(swedish_earnings_skk*SEK) total_earnings_in_usd
FROM raw_financials rf
LEFT JOIN ( SELECT  date, 
                    MIN(CASE WHEN  currency = 'CAD' THEN conversion_to_usd END) CAD,
                    MIN(CASE WHEN  currency = 'GBP' THEN conversion_to_usd END) GBP,
                    MIN(CASE WHEN  currency = 'SEK' THEN conversion_to_usd END) SEK
            FROM exchange_rates 
            WHERE currency IN ('CAD','GBP','SEK')
            GROUP BY date) er
ON rf.date = er.date
WHERE title LIKE '%Gamers%' AND rf.date= '2012-12-01'
GROUP BY title

Answer 3

SELECT
    t.title, 
    t.usd,
    t.cad,
    t.gbp,
    t.skk,
    t.usd + t.cad*er_cad.conversion_to_usd + t.gbp*er_gbp.conversion_to_usd + t.skk*er_skk.conversion_to_usd AS total
FROM
(
    SELECT
        title, 
        SUM(us_earnings_usd) AS usd,
        SUM(cad_earnings_cad) AS cad,
        SUM(uk_earnings_gbp) AS gbp,
        SUM(swedish_earnings_skk) AS skk,
        FROM raw_financials 
        WHERE date="2012-12-01"
        GROUP BY title
) t
INNER JOIN exchange_rates er_cad ON er_cad.date = t.date and er_cad.currency = 'CAD'
INNER JOIN exchange_rates er_gbp ON er_gbp.date = t.date and er_gbp.currency = 'GBP'
INNER JOIN exchange_rates er_skk ON er_skk.date = t.date and er_skk.currency = 'SKK'

Anyways, I think that there is something wrong with your sales table. You should have only one earnings column (not a separate one for each currency). As you see, for each row you have to fill only one column with an actual value and the rest are zero. It will be enough to have the partner_share_currency column and a generic earnings column.