Case when statement not working as an aggregate expression

I'm getting an error in my code saying that my case when part of the query has to be an aggregate expression or appear in GROUP BY clause

the code that I currently have is:

when advertiser_currency
like 'GBP' then sum(try_Cast(revenue_adv_currency AS DOUBLE)*1.15)
else sum(TRY_CAST(revenue_adv_currency AS DOUBLE)*1)
end AS amount_spent_EUR

How can I make this into an aggregate function?

thank you!

1 answer

  • answered 2019-10-02 16:00 Juan Carlos Oropeza

    You put the case inside the aggregation

     SUM ( case when advertiser_currency like 'GBP' 
                then try_Cast(revenue_adv_currency AS DOUBLE) * 1.15
                else try_Cast(revenue_adv_currency AS DOUBLE) * 1.0
         ) AS amount_spent_EUR