BrevFeed
Sign in

24. FILTER and conditional aggregation

📖 Reading · 5 min
💡 Every code box below is live — edit it and hit Run.

The problem: several differently-filtered aggregates, one row per group

The shape of the problem: "total films, and separately, R-rated films, and separately, PG-rated films" — three different filter conditions, one aggregate function each, all in a single row of output. There are two idiomatic ways to write this; this lesson covers both and when to prefer each.

CASE-inside-aggregate (the portable way)

SELECT
    store_id,
    count(*) AS total_rentals,
    count(CASE WHEN return_date IS NULL THEN 1 END) AS outstanding,
    sum(CASE WHEN return_date IS NOT NULL THEN 1 ELSE 0 END) AS returned
FROM rental r
JOIN staff s ON s.staff_id = r.staff_id
GROUP BY store_id;

Recall from Module 1's CASE lesson exactly how this works: CASE ... END with no ELSE produces NULL for non-matching rows, and count() skips NULLs — so count(CASE WHEN cond THEN 1 END) counts only rows where cond was true. This pattern is standard SQL, works identically on every engine, and is worth knowing cold even though the next form is often cleaner to read.

FILTER (the Postgres/standard-SQL-2003 way)

SELECT
    store_id,
    count(*) AS total_rentals,
    count(*) FILTER (WHERE return_date IS NULL) AS outstanding,
    count(*) FILTER (WHERE return_date IS NOT NULL) AS returned
FROM rental r
JOIN staff s ON s.staff_id = r.staff_id
GROUP BY store_id;

agg(...) FILTER (WHERE condition) applies condition only to that one aggregate — every other aggregate in the same SELECT list still sees all the group's rows unless it has its own FILTER. This is directly equivalent to the CASE-inside-COUNT pattern above, but reads more clearly once you're filtering several different aggregates in one query, because the condition sits next to the aggregate it applies to instead of being buried inside a CASE expression.

FILTER is part of the SQL standard (added in SQL:2003) and Postgres supports it natively — but it's worth knowing MySQL didn't support FILTER until fairly recently and older codebases/tutorials still lean on CASE for this reason. Recognize both; this course uses FILTER going forward when the choice is purely stylistic, since it's the cleaner spelling in Postgres.

Why this beats separate queries or self-joins

Before either pattern, the naive approach to "several differently filtered counts, one row" is either N separate queries (N round trips, N full scans) or joining N differently-filtered subqueries together (the multi-join fan-out risk from Module 4). FILTER/CASE conditional aggregation computes all the filtered aggregates in one pass over the data, which is both simpler to write and meaningfully cheaper to execute — this is the answer to "how do I get a pivot-style one-row summary" for the vast majority of real reporting queries, well before you'd reach for the heavier GROUPING SETS machinery from the previous lesson.

FILTER with different conditions per aggregate, in one query

The real payoff — five genuinely different conditions, five aggregates, one row per group, one pass over the table:

SELECT
    store_id,
    count(*) AS total_rentals,
    count(*) FILTER (WHERE return_date IS NULL) AS outstanding,
    avg(EXTRACT(DAY FROM return_date - rental_date))
        FILTER (WHERE return_date IS NOT NULL) AS avg_rental_days,
    count(*) FILTER (WHERE rental_date > '2022-08-01') AS recent_rentals,
    count(DISTINCT customer_id) FILTER (WHERE return_date IS NULL) AS customers_with_outstanding
FROM rental r
JOIN staff s ON s.staff_id = r.staff_id
GROUP BY store_id;

Each FILTER is completely independent — nothing here requires the conditions to relate to each other, unlike GROUPING SETS/ROLLUP/CUBE which are specifically about subtotaling the same measure across different groupings of the same dimension columns. Reach for FILTER/CASE conditional aggregation when you want several distinct measures; reach for ROLLUP/CUBE when you want the same measure at several levels of grouping.

Check yourself

  1. Write count(*) FILTER (WHERE rating = 'R') as an equivalent CASE-inside-count() expression.
  2. Why does FILTER read more clearly than nested CASE once you have three or more differently-filtered aggregates in one query?
  3. When would you reach for GROUPING SETS/ROLLUP instead of FILTER-based conditional aggregation, and vice versa?
← Previous Next →