sqlpostgresqlnullif

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

Solution

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

    Try it yourself:

    SELECT NULL * 100