CODEWITHSUNDEEP
postgresql
idazuwaika
idazuwaika

Reputation: 3019

Coalesce for zero instead of null

I have a simple company table which inconsistently use column name turnover and revenue. The table currently looks like this:

company, turnover, revenue
A, 10000, 0
B, 0, 2500
C, 0, 3000
4, 23000, 0

I know how to use coalesce to choose between null and value, but there the value to be discarded is zero instead of null.

The end result that I want is:

company, revenue
A, 10000
B, 2500
C, 3000
D, 23000

The query that I'm imagining is:

select company, coalesce_for_zero(turnover, revenue) as revenue from
company_profile;

How do I write a query that can achieve coalesce-like results for zero?

Upvotes: 58

Views: 52537

Answers (2)

lolacoco
lolacoco

Reputation: 101

You can coalesce with 0 as a parameter

coalesce(turnover, revenue, 0)

Upvotes: -4

clemens
clemens

Reputation: 17721

You can combine NULLIF with COALESCE:

SELECT company, COALESCE(NULLIF(turnover, 0), revenue) AS revenue FROM company_profile;

NULLIF returns NULL if it's arguments are equal, and the first argument otherwise.

Upvotes: 115

Related Questions