CODEWITHSUNDEEP
sqlpostgresqlnullif
Hommer Smith
Hommer Smith

Reputation: 27852

Multiply in SQL just if result is not NULL

I have the following query:

SELECT (COUNT(DISTINCT CASE WHEN houses.level = 'top' THEN users.id END)::float / NULLIF(COUNT(DISTINCT users.id)::float, 0)) 
FROM users
LEFT JOIN houses ON houses.user_id = users.id

And instead of a ratio, I want to return a percentage, so I changed it to:

SELECT (COUNT(DISTINCT CASE WHEN houses.level = 'top' THEN users.id END)::float / NULLIF(COUNT(DISTINCT users.id)::float, 0)) * 100
FROM users
LEFT JOIN houses ON houses.user_id = users.id

However, if the denominator (NULLIF(COUNT(DISTINCT users.id)::float, 0)) returns null, it means I will be multiplying NULL * 100. How can I just return NULL without multiplying if the denominator is 0 (and therefore null because of the NULLIF).

Upvotes: 0

Views: 1619

Answers (2)

Caius Jard
Caius Jard

Reputation: 74660

If you want NULL to represent 0%, use COALESCE:

SELECT COALESCE((COUNT(DISTINCT CASE WHEN houses.level = 'top' THEN users.id END)::float / NULLIF(COUNT(DISTINCT users.id)::float, 0)) * 100, 0) ...

COALESCE returns the first non-null thing it encounters, working from left to right

Otherwise, go for what Nobita recommends ; let null be null

Upvotes: 0

Nobita
Nobita

Reputation: 23713

It's ok to multiply an INT with NULL. The result will still be NULL.

Try it yourself:

SELECT NULL * 100

Upvotes: 2

Related Questions