The FILTER Clause for Conditional Aggregation in Window Functions

1. The classic problem: CASE WHEN inside aggregate functions

To only consider certain rows within an aggregation, most developers intuitively reach for a combination of COUNT or SUM and an inner CASE WHEN expression. The aggregate function then only counts or sums the rows for which the CASE expression returns a value other than null, while every other row effectively drops out of the aggregation, since null values are ignored by COUNT, SUM, and most other aggregate functions.

This pattern works reliably and is available in practically every database, but it carries a noticeable readability cost once several such conditional aggregations sit side by side in the same query. The reader first has to mentally unwrap the CASE expression for every column before the actual aggregate function and its condition become visible, which quickly turns into an unwieldy wall of nested expressions with five or six parallel metrics.

SELECT
    customer_id,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_count,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_count,
    SUM(CASE WHEN status = 'completed' THEN total_amount END) AS completed_revenue
FROM orders
GROUP BY customer_id;

2. The FILTER clause: a declarative condition instead of CASE nesting

The FILTER clause is written directly after an aggregate function and contains a WHERE condition that determines which rows feed into the aggregation. Syntactically, FILTER (WHERE condition) is therefore an integral part of the aggregate function itself, not a transformation of the value being aggregated the way CASE WHEN is. This separation makes it immediately clear which aggregate function is combined with which condition, without the reader first having to decode the inner expression.

Functionally, FILTER is exactly equivalent to the COUNT CASE WHEN combination, the result never differs. The difference lies purely in readability and, as shown in the performance section, sometimes in the execution plan the optimizer produces. For teams writing many conditional aggregations in reporting queries, switching to FILTER is therefore usually a pure improvement with no functional downside.

SELECT
    customer_id,
    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_count,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_count,
    SUM(total_amount) FILTER (WHERE status = 'completed') AS completed_revenue
FROM orders
GROUP BY customer_id;

3. Several parallel metrics in a single query

The real value of the FILTER clause shows up in reporting queries that need to return many different metrics for the same grouping in a single result row, for example a dashboard that shows order count by status, revenue by status, and average order value by status per customer all at once. With CASE WHEN, every additional metric would mean another layer of nesting, with FILTER every row in the SELECT list stays independently readable.

Another practical advantage is that different aggregate functions with different filter conditions can be freely combined without needing to adjust the WHERE clause of the query itself. A single GROUP BY query can thus deliver counts, sums, and averages for any number of different subsets of the same underlying set at once, which is particularly valuable for dashboard backends, where exactly this kind of multi metric query is the norm rather than the exception.

SELECT
    customer_id,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders,
    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders,
    ROUND(AVG(total_amount) FILTER (WHERE status = 'completed'), 2)
        AS avg_completed_value,
    SUM(total_amount) FILTER (WHERE order_date >= CURRENT_DATE - INTERVAL '30 days')
        AS revenue_last_30_days
FROM orders
GROUP BY customer_id;

4. FILTER combined with window functions

The FILTER clause is not limited to classic GROUP BY aggregation, it can also be attached to aggregate functions used as window functions with an OVER clause. That makes it possible to compute running totals or moving averages that only consider part of the rows within the window, without needing an additional subquery or a second CASE expression.

A practical example is a running total of revenue from completed orders per customer, ordered by date, while cancelled orders are consistently ignored in the running total yet still appear as their own row in the result. This combination of FILTER and OVER is also possible with CASE WHEN, but becomes even harder to read due to the additional nesting inside window function syntax than it already is in the plain GROUP BY case.

SELECT
    order_id,
    customer_id,
    order_date,
    status,
    total_amount,
    SUM(total_amount) FILTER (WHERE status = 'completed')
        OVER (PARTITION BY customer_id ORDER BY order_date)
        AS running_completed_revenue
FROM orders
ORDER BY customer_id, order_date;

5. How the optimizer treats FILTER compared to CASE WHEN

In PostgreSQL, the FILTER clause often leads to a more efficient execution plan than the equivalent CASE WHEN formulation, because the optimizer recognizes the filter condition directly as standalone information and can partly use it for a more targeted selection of relevant rows, instead of only resolving the condition inside the aggregate function. With several simultaneously used filter conditions that partially overlap, the optimizer can also combine shared subsets more efficiently internally.

In practice, runtime differences on small to medium data volumes are usually small and rarely the decisive reason to switch. On very large reporting tables with many parallel FILTER conditions, it is still worth checking EXPLAIN ANALYZE, since even small differences in the execution plan can have a noticeable impact on the overall runtime of a reporting query once millions of rows are involved.

6. Database availability: standard SQL vs. proprietary alternatives

The FILTER clause has been part of the SQL standard since SQL:2003 and is fully implemented in PostgreSQL since version 9.4, as well as in SQLite since a comparatively early version. Both databases support the combination with window functions exactly as shown in the previous section, with no restrictions compared to plain GROUP BY usage.

MySQL and Microsoft SQL Server, on the other hand, still do not natively support the FILTER clause today, and the CASE WHEN combination remains the only available solution there. Oracle also has no FILTER in the sense of the SQL standard, but offers the KEEP addition together with special analytic functions as a partially similar but syntactically completely different alternative. Anyone writing database agnostic code should therefore deliberately use FILTER only for PostgreSQL or SQLite specific code paths and stick to CASE WHEN everywhere else.

7. FILTER combined with DISTINCT and multiple conditions

FILTER combines seamlessly with COUNT DISTINCT, for example to count the number of different products a customer bought exclusively in cancelled orders, while other order statuses are ignored for that same expression. This combination of DISTINCT and FILTER within the same aggregate function is also possible with a pure CASE WHEN solution, but noticeably more error prone, since a misplaced parenthesis can quickly produce a semantically wrong but still syntactically valid expression.

More complex filter conditions with several AND and OR combinations can be formulated inside the FILTER clause exactly the way they would inside a normal WHERE clause, including subqueries and function calls. That makes FILTER a full alternative for nearly any conceivable condition, not just simple equality checks like in the earlier examples.

SELECT
    customer_id,
    COUNT(DISTINCT product_id) FILTER (
        WHERE status = 'cancelled' AND total_amount > 50
    ) AS distinct_products_cancelled_high_value
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
GROUP BY customer_id;

8. Do not confuse FILTER and WHERE: different evaluation points

A common misunderstanding treats FILTER as a plain replacement for WHERE. In reality the two operate on completely different levels: WHERE removes rows from the query entirely before any grouping happens, and therefore affects every aggregate function in the SELECT list equally. FILTER, on the other hand, only restricts a single aggregate function, while every other aggregate function in the same query still sees the full, unfiltered set of rows for that group.

This difference becomes relevant as soon as a query needs to return both a total count across all rows and a conditional partial count. If the condition is mistakenly placed in a WHERE clause instead of FILTER, not only does the desired partial count disappear, but the intended total count also shrinks down to the filtered subset, and groups that contain only rows outside the condition drop out of the result entirely because of the WHERE clause, whereas with FILTER they correctly survive with a value of zero for the conditional metric.

-- Wrong: WHERE removes rows for the entire query
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
WHERE status = 'completed';  -- total_orders only counts completed!

-- Correct: FILTER only restricts the second metric
SELECT
    customer_id,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders
FROM orders
GROUP BY customer_id;

9. Migrating existing CASE WHEN code to FILTER

When migrating existing reporting queries from CASE WHEN to FILTER, a systematic approach pays off: first identify every aggregate function whose inner expression consists purely of a CASE WHEN construct with exactly one condition and an implicit or explicit ELSE null, since only those cases can be mechanically converted to FILTER without any behavior change. More complex CASE expressions with several WHEN branches and different return values are not pure filter logic and should not be rushed into a rewrite.

Since FILTER simply results in a syntax error on databases without native support, an automated test against every database version used in production is recommended before any migration. In mixed environments where both PostgreSQL and MySQL run side by side, a central query builder or an abstraction layer that automatically switches between FILTER and the equivalent CASE WHEN formulation depending on the target database proves useful.

10. Summary

11. FAQ: The FILTER Clause: Key Takeaways