Postgres: Filtering with COUNT()

Context

Doing a COUNT() in SQL is pretty simple, but sometimes you want to return several counts at once with different filters. To achieve this, you would typically do multiple queries, optionally with a WITH query.

Thankfully, there's a way simpler way to do this in PostgreSQL, with FILTER.

Let's consider we have a simple table (racoons) with a few fields, and we want to return a caretaker_id, the total number of racoons taken care by this person, and the number of racoons that have been released to the wilds.

It's as simple as this:

SELECT   r.caretaker_id,
         COUNT(*) as total,
         COUNT(*) FILTER (WHERE released = true) as released
FROM     racoons r
GROUP BY r.caretaker_id;

This page has a bit more information, especially if you're working with a PostgreSQL database prior to 9.4.