Conditional aggregation with FILTER and CASE

PostgreSQL aggregate functions support a FILTER clause.

The syntax is:

For example:

You should see:

The important part is:

Only rows satisfying that condition are passed to this particular COUNT().

Consider:

WHERE removes rows from the query before aggregation.

Conceptually:

Now consider:

You should see:

The query still has access to all orders.

Only the second aggregate has an additional filter.

Conceptually:

A useful distinction is:

This becomes especially useful when different aggregates need different subsets of the same data.

Run:

You should see:

One query has produced several different summaries.

This pattern is common in:

FILTER can be attached to other aggregate functions too.

For example:

You should see:

The first SUM() receives only delivered rows.

The second receives only cancelled rows.

The same pattern can be used with aggregates such as:

Conditional aggregation becomes even more useful when combined with grouping.

Imagine we want, for each year:

Run:

You should see:

PostgreSQL first creates one group for each year.

Then, inside every year group:

counts all orders,

while:

counts only delivered orders in that group.

Conceptually:

GROUP BY determines the year group.

FILTER determines which rows inside that group participate in each aggregate.

Conditional aggregation can also be written using a CASE expression.

For example:

This also returns:

To understand why, remember how CASE works.

For a delivered row:

returns:

For any other status, there is no ELSE, so the expression returns:

The aggregate therefore receives values resembling:

And because:

counts non-NULL values, only delivered rows are counted.

This version looks reasonable:

but it does not count only delivered rows. It returns:

Why?

For non-delivered rows, the CASE expression returns:

But 0 is not NULL.

COUNT(expression) counts both:

because both are non-NULL values.

So this expression counts every row.

If you use COUNT(CASE ...), the nonmatching rows should normally produce NULL:

For this simple counting case, FILTER is usually clearer:

CASE is also commonly used with SUM().

For example:

This returns:

Here, ELSE 0 is appropriate.

Each delivered order contributes:

and every other order contributes:

So the sum is the number of delivered orders.

Conceptually:

CASE is useful when the condition should determine what value participates in the calculation.

For example:

For a delivered row:

For another status:

The same result can often be written more directly using FILTER:

Both return 28836260.00.

The two approaches express the problem differently:

Compare:

with:

The second version states the intent directly:

Similarly:

reads naturally as:

For straightforward conditional aggregation in PostgreSQL, FILTER is therefore often easier to read.

This does not mean you should assume that FILTER is always faster than an equivalent CASE expression.

If performance matters for a particular query, inspect the execution plan and measure it rather than relying on a general rule.

CASE remains useful when the output expression itself changes based on multiple conditions.

For example:

Here, we are doing more than simply deciding whether a row participates.

Different rows contribute different calculated values.

That is naturally expressed with CASE.

Suppose the query is:

Every aggregate uses exactly the same condition.

In that case, the query is usually clearer as:

There is no need for a separate FILTER on every aggregate.

Use FILTER when different aggregates need different subsets of the rows.

For example:

Here, a single global WHERE clause cannot express what we need because each aggregate intentionally sees a different set of rows.

Think of the three tools separately:

And when using CASE:

These ideas can be combined in one query:

You should see:

WHERE removed the pending orders, GROUP BY split the rest by year, and each FILTER narrowed one aggregate further.

The important point is:

Conditional aggregation lets different aggregate functions calculate summaries from different subsets of the same rows, often allowing one query to produce an entire dashboard or API summary.