FILTER (WHERE …) on aggregates

Restricts which rows a single aggregate sees, so one query can compute several conditional totals.

Syntax

aggregate(expr) FILTER (WHERE condition)
-- portable equivalent:
aggregate(CASE WHEN condition THEN expr END)

FILTER attaches a row condition to one aggregate. Other aggregates in the same query are unaffected. It is the cleanest way to pivot values into columns.

count(*) FILTER (WHERE status = 'paid') counts the matching rows; with no matching rows it returns 0.
sum(x) FILTER (...) returns NULL when no rows match (like any sum over zero rows).
Works with every aggregate (avg, string_agg, array_agg…) and with aggregate window functions.
The CASE form is equivalent and portable: count(CASE WHEN cond THEN 1 END), sum(CASE WHEN cond THEN x ELSE 0 END).

Examples

Pivot statuses into columns shop

SELECT channel,
       count(*) AS orders,
       count(*) FILTER (WHERE status = 'delivered') AS delivered,
       count(*) FILTER (WHERE status = 'cancelled') AS cancelled,
       round(100.0 * count(*) FILTER (WHERE status = 'cancelled') / count(*), 2) AS cancel_pct
FROM orders
GROUP BY channel
ORDER BY channel;

One row per channel; each FILTER count sees only its own rows.

Year columns starter

SELECT p.category,
       sum(o.quantity) FILTER (WHERE o.order_date < '2025-07-01') AS units_h1,
       sum(o.quantity) FILTER (WHERE o.order_date >= '2025-07-01') AS units_h2
FROM orders o
JOIN products p USING (product_id)
GROUP BY p.category
ORDER BY p.category;

A category with no orders in a half shows NULL there (sum over zero rows).

Same thing with CASE shop

SELECT channel,
       sum(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       sum(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
GROUP BY channel
ORDER BY channel;

Portable to engines without FILTER.

Pitfalls

Putting the condition in WHERE, which filters every aggregate, not just one.
Expecting sum(...) FILTER to return 0 when nothing matches (it returns NULL; wrap in COALESCE).
count(CASE WHEN cond THEN 1 ELSE 0 END) counts every row (0 is not NULL); use sum for 1/0 or drop the ELSE.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQLite: Supports FILTER on aggregates (3.30+).
MySQL: No FILTER clause; use SUM(CASE WHEN ...) or SUM(cond), since booleans are 1/0.
SQL Server: No FILTER clause; use the CASE form.
BigQuery: No FILTER clause; use COUNTIF(cond) or SUM(IF(cond, x, 0)).
Snowflake: Use COUNT_IF(cond) or SUM(IFF(cond, x, 0)).