title: Summing sales across different currencies in SQL
body: <p>This is a follow-up to a previous questions I asked: <a href="https://stackoverflow.com/questions/8885572/using-a-group-by-statement-to-sum-rows">Using a GROUP BY statement to sum rows</a>. I have a table with sales of different currencies, and I am using a GROUP BY  statement to sum up the numbers per title.</p>
  
  <pre><code>mysql&gt; 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 |
  +--------+-----------------+------------------+------------------+------------------+
  </code></pre>
  
  <p>Finally, I need to sum up the fields to get the total sales in USD. For this, I have an additional table called <code>exchange_rates</code>. This is how it can be queried:</p>
  
  <pre><code>mysql&gt; 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 |
  +----------+-------------------+
  </code></pre>
  
  <p>How would I combine these using SQL to get:</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>Update</strong>: I have updated the SQL in the question.</p>