David Bentzon-Ehlers - 1 year ago 114

SQL Question

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`

`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 Source

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');
```