SQL – sum operations within case when statement

I need to aggregate some sessions by day, country etc. The table has a transaction amount for each session (in local currency) and a field with the exchange_rate to EUR for the time of the transaction. Like this:

amount | currency | exchange_rate | date | country 

I ran sum(amount/exchange_rate), however, for roughly 0.5% of the rows the value for exchange_rate is 0 and therefore it throws the error “cannot divide by 0”.

I tried to run it with case when:

sum(case when exchange_rate = 0 then sum(amount) else sum(amount/exchange_rate) end) as volume

But apparently nested sum’s are not allowed. Does anybody have an idea as to how I can get the result the above should logically produce, but without nested sums in a case when statement?

Answer

Assuming that an exchange rate of 0 indicates that the amount is in EUR currency already, you can do:

sum(amount / case when exchange_rate = 0 then 1 else exchange_rate end)