Filters in SQL Queries

As the saying goes, live and learn. I recently discovered a new SQL function I wasn't aware of before: filters in COUNT queries. But let's start from the beginning.

In most of my projects, I rarely used COUNT queries. And if I did, it was only for a single value. It just happened that way.

Today, however, I needed to count several values from one table: total queries, successful queries, and error queries.

Making three queries with the WHERE condition wasn't the best idea, as their number multiplied by the number of endpoints, resulting in more than a dozen COUNT operations for loading a single page, which isn't great.

I found the solution very quickly: using the FILTER keyword:

SELECT COUNT(*) AS total,
COUNT(*) FILTER (WHERE status = 'done') AS done,
COUNT(*) FILTER (WHERE status = 'failed') AS failed
FROM requests

The result is one query, one pass through the data, and neat, readable SQL without SUM(CASE WHEN ...).

I am constantly reminded that even familiar tools have features that can significantly simplify life. The key is to keep looking for them 😊.