Coalesce for zero instead of null

Question

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?

Answer 1

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.