FloatingRock FloatingRock - 3 months ago 11
SQL Question

Using SQL CASE/WHEN output for calculation

I've got the following query that I'd like to use in order to convert an amount from various currencies into USD:

SELECT
currency,
CASE currency
WHEN 'SAR' THEN 0.266
WHEN 'EGP' THEN 0.113
WHEN 'USD' THEN 1
WHEN 'JOD' THEN 1.411
WHEN 'GBP' THEN 1.311
WHEN 'BHD' THEN 2.652
WHEN 'AED' THEN 0.272
WHEN 'EUR' THEN 1.111
WHEN 'QAR' THEN 0.275
WHEN 'KWD' THEN 3.315
ELSE 0
END as in_usd,
SUM(amount)*in_usd as total_in_usd
FROM
mytable
WHERE
created_at >= current_date - '7 days'::INTERVAL
GROUP BY
currency


This fails with the message
in_usd does not exist
.

The expected output is a list of currencies and their totals:

currency total_in_usd
USD 100.00
AED 59.00
GBP 143.01
...

Answer

Put the CASE expression part in a derived table. Do GROUP BY on it's result:

select currency, in_usd, SUM(amount) * in_usd as total_in_usd
from
(
SELECT 
    currency, 
    CASE currency 
      WHEN 'USD' THEN 1
      WHEN 'SAR' THEN 0.266
      WHEN 'EGP' THEN 0.113
      WHEN 'USD' THEN 1
      WHEN 'JOD' THEN 1.411
      WHEN 'GBP' THEN 1.311
      WHEN 'BHD' THEN 2.652
      WHEN 'AED' THEN 0.272
      WHEN 'EUR' THEN 1.111
      WHEN 'QAR' THEN 0.275
      WHEN 'KWD' THEN 3.315
      ELSE 0 
    END as in_usd,
    amount
FROM 
    mytable
WHERE 
    created_at >= current_date - '7 days'::INTERVAL 
)
GROUP BY 
    currency, in_usd
Comments