FILTER Clause on Aggregates in SQL

The FILTER clause attaches a condition directly to an aggregate function, restricting which rows that specific aggregate counts or sums, while leaving every other aggregate in the query unaffected.

The scenario: you're building a regional order dashboard. For each region, you need total orders, completed orders, and completed revenue — all in one row. You already know how to do this with CASE WHEN inside an aggregate. FILTER is a cleaner way to express the same thing. Instead of embedding a CASE expression inside the function, you place the condition after it:

Each FILTER is independent. COUNT(*) FILTER (WHERE status = 'delivered') only counts delivered orders. The plain COUNT(*) still counts everything. A row excluded by one FILTER is still visible to all other aggregates in the same SELECT list.

The CASE WHEN equivalent for the completed count:

Both produce the same number. FILTER is just easier to read — the condition sits right next to the function it constrains, instead of being buried inside a CASE expression wrapped around the argument.

FILTER works with every standard aggregate: COUNT, SUM, AVG, MIN, MAX, and others. When you have several conditional aggregates in one SELECT list, each targeting a different condition, FILTER makes the query much easier to scan:

The same query with CASE WHEN would have three nested expressions to parse. With FILTER, each line states its condition plainly.

The one thing that trips people up

FILTER excludes rows from the aggregate entirely. CASE WHEN excludes them by substituting NULL, which COUNT then ignores. For COUNT, the result is the same. For AVG, it can differ in subtle ways — FILTER removes the row from both the sum and the count, while CASE WHEN removes the value from the sum but not necessarily the count, depending on how it's written. For standard AVG(CASE WHEN ... END), behavior matches FILTER. Just be deliberate about which rows you want included in each calculation.

When to use FILTER vs CASE WHEN

For a SELECT list with several conditional aggregates targeting different conditions, FILTER is easier to scan — each function states its condition plainly on one line. For a single conditional aggregate embedded inside a complex expression, CASE WHEN often integrates more naturally. Both are correct; the choice is about which makes the query easier for someone else to read. On teams that use PostgreSQL, FILTER is increasingly the standard for multi-condition breakdowns.