SQL - sum operations within case when statement

Question

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?

@The Impaler; I want to replace the 0 with 1, as GMB suggested — marley_maldon
– marley_maldon, Commented Jul 21, 2020 at 15:48

GMB · Accepted Answer · 2020-07-20 15:00:18Z

3

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)

answered Jul 20, 2020 at 15:00

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-07-20 15:00:54Z

0

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

answered Jul 20, 2020 at 15:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

The Impaler · Accepted Answer · 2020-07-20 15:00:56Z

0

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

answered Jul 20, 2020 at 15:00

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Collectives™ on Stack Overflow

SQL - sum operations within case when statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related