Multiply in SQL just if result is not NULL

Question

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).

Why are you concerned if `NULL` is multiplied by 100? – Gordon Linoff Jan 09 '19 at 12:25 — Gordon Linoff, Jan 09 '19 at 12:25

score 2 · Accepted Answer · answered Jan 09 '19 at 12:23

2

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

Try it yourself:

SELECT NULL * 100

answered Jan 09 '19 at 12:23

Nobita

23,519
11
58
87

Also works as expected in MySQL. Thanks! – Tumo Masire Feb 07 '23 at 10:58

score 0 · Answer 2 · answered Jan 09 '19 at 12:24

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

Multiply in SQL just if result is not NULL

2 Answers2