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?
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)
I think you want:
sum(case when exchange_rate = 0 then amount else amount/exchange_rate end) as volume
Or for a bit less typing:
sum(amount / coalesce(nullif(exchange_rate, 0), 1)) as volume
Essentially you want to consider the exchange rate as 1 when it's zero. You can do:
sum(case when exchange_rate = 0
then amount
else amount/exchange_rate
end
) as volume