Using case when along with where in SQL?-CodePudding

I have the following table. I need the SQL for amount of sales in 2016 in euros? I am trying the following code but I am getting syntax error!

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

Order_Items:

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   |

CodePudding user response：

As written, pound is assumed to be a column - you need to quote it as a string literal.

Additionally, case is an expression that returns a value, set is not valid syntax.

Select sum(quantity*price) as total,
price * 
  case 
    when lower(currency) = 'pound' 
  then 1.1 else 1 end as rate
from order_items
where YEAR(order_date) = 2016;

CodePudding user response：

I think this is best done with a subquery. In the subquery, you first convert any 'pound' prices into Euros. The outer query computes the total 2016 sales in Euros.

select sum(quantity*euro_price) as euro_sales_2016
from (
    select quantity,
           case when lower(currency) = 'pound'
                then price * 1.1
                else price 
           end as euro_price
    from order_items 
    where YEAR(order_date) = 2016
) a;