How can I use CASE with a WHERE clause in SQL?

Data and Databases
1

I’m trying to get total sales in euros for the year 2016, but I’m facing a syntax error with my SQL query. I need to convert pounds to euros using an exchange rate. Here’s my attempt:

Select 
    sum (quantity * price),
    case 
        when lower(currency) = 'pound' 
            then set price = 1.1 * price
    end
from 
    order_items
where 
    year(order_date) = 2016;

The structure of my table is:

order_item_key order_key order_date customer_key prime_status quantity price currency
xyz ksp 2020-06-01 123abc456 0 1 35 pound
abc sdl 2019-08-15 567fjs290 1 2 2.3 euro
wrt ter 2016-08-15 567fjs460 1 2 2.3 euro
tyu qwe 2016-08-15 567fjs350 1 3 4.3 euro

Can someone help me fix it?

2

Hey, quick question - is that 1.1 exchange rate actually right for 2016? Also, what happens if you’ve got null currency values in your data? You might wanna throw in a check for those.

3

Your problem is using set price = 1.1 * price inside the CASE statement - that’s assignment syntax, not calculation syntax. You need to do the conversion right inside the SUM function instead.

Here’s the fix:

SELECT 
    SUM(quantity * 
        CASE 
            WHEN LOWER(currency) = 'pound' THEN price * 1.1
            ELSE price
        END
    ) AS total_sales_euros
FROM order_items
WHERE YEAR(order_date) = 2016;

This way the CASE converts pounds to euros (multiply by 1.1) and leaves euro prices alone, then multiplies everything by quantity inside the SUM.

4

yeah, you can’t use set in a case statement like that. case just returns a value, not assigns. move the case expr into your sum calc instead of trying to change the price directly.