David Bentzon-Ehlers David Bentzon-Ehlers - 5 months ago 27
SQL Question

PostgreSQL: How to avoid division by zero when using case statements

I am trying to divide two case statements. How do I avoid the occasional division by zero? I couldn't seem to figure out the

NULLIF
method.

SELECT
ROUND(100 * SUM(CASE WHEN be.event IN ('a') THEN 1 ELSE 0 END) / SUM(CASE WHEN be.event IN ('b', 'a') THEN 1 ELSE 0 END), 2) AS "% Instant Book Today"
FROM booking_events be
WHERE
be.created_at >= current_date AND
be.created_at < current_date + interval '1 day';


What is the most efficient way of solving this problem?

Answer

You can use NULLIF() as:

SELECT ROUND(100 * SUM(CASE WHEN be.event IN ('a') THEN 1 ELSE 0 END) / 
                   NULLIF(SUM(CASE WHEN be.event IN ('b', 'a') THEN 1 ELSE 0 END), 0), 2
            ) AS "% Instant Book Today"

Or, alternatively, the denominator is always non-negative, just remove the ELSE 0:

SELECT ROUND(100 * SUM(CASE WHEN be.event IN ('a') THEN 1 ELSE 0 END) / 
                   SUM(CASE WHEN be.event IN ('b', 'a') THEN 1 END), 2
            ) AS "% Instant Book Today"

Or, write the query as:

SELECT ROUND(AVG(CASE WHEN be.event IN ('a') THEN 100.0 ELSE 0 END),
             2) as "% Instant Book Today"
FROM booking_events be
WHERE be.created_at >= current_date AND
      be.created_at < current_date + interval '1 day' AND
      be.event IN ('a', 'b');