CODEWITHSUNDEEP
sqlaggregation
Kastriot
Kastriot

Reputation: 27

Calculations with NULL value

I am trying to calculate the difference between two columns, but sometimes one of the columns has NULL value and SQL is ignoring that.

Right now I am using this: if("source indicator" = 'Total OPEX', ("Eur Amount" -"EUR Target") * -1, "Eur Amount" -"EUR Target") as "Diff."

Is there any other way to calculate this (please note that OPEX value) and get the expected results?

Thanks

Upvotes: 0

Views: 183

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use case and coalesce():

(case when "source indicator" = 'Total OPEX'
      then coalesce("Eur Amount", 0) - coalesce("EUR Target", 0) * -1
      else coalesce("Eur Amount", 0) - coalesce("EUR Target", 0)
 end)

The advantage of case is that it is ANSI-standard SQL, supported by pretty much all databases. if() is database-dependent.

Upvotes: 4

Related Questions