COUNT(*) FILTER (WHERE ...): Conditional Aggregates in One Pass

When you need several metrics under different conditions in one query — say, orders with status paid and separately refunded — the FILTER (WHERE ...) clause computes them all in a single pass over the data. It is standard SQL and far more readable than the old SUM(CASE WHEN ...) trick.

What FILTER is and why it matters

FILTER (WHERE ...) attaches to any aggregate function and narrows the rows that feed into it. The aggregate sees only rows where the condition is true; the rest are simply ignored, as if they were not part of the group.

SELECT
  count(*)                                  AS orders_total,
  count(*) FILTER (WHERE status = 'paid')   AS paid_orders,
  count(*) FILTER (WHERE status = 'refunded') AS refunded_orders
FROM orders;

Key properties:

• FILTER is applied at aggregation time, on top of the query-wide WHERE.
• Within a single SELECT, each aggregate can carry its own FILTER.
• The condition is a full boolean expression over the row's columns.

Several segments in one pass

The real win is assembling a wide report with a dozen metrics without scanning the table multiple times and without self-joins. It pairs naturally with GROUP BY.

SELECT
  u.country,
  count(*)                                          AS users_total,
  count(*) FILTER (WHERE o.id IS NOT NULL)          AS users_with_orders,
  sum(o.amount) FILTER (WHERE o.status = 'paid')    AS revenue_paid,
  avg(o.amount) FILTER (WHERE o.status = 'paid')    AS avg_paid_order
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.country;

One pass, one execution plan — the engine computes every aggregate at the same time. That is both faster and clearer than three near-identical queries stitched together with JOIN.

FILTER versus CASE inside the aggregate

The same result used to be written with CASE inside SUM/COUNT. Compare:

-- Old style: CASE inside the aggregate
SELECT
  count(CASE WHEN status = 'paid' THEN 1 END)     AS paid_orders,
  sum(CASE WHEN status = 'paid' THEN amount END)  AS revenue_paid
FROM orders;

-- Modern equivalent with FILTER
SELECT
  count(*)      FILTER (WHERE status = 'paid') AS paid_orders,
  sum(amount)   FILTER (WHERE status = 'paid') AS revenue_paid
FROM orders;

FILTER wins on readability: the condition is separated from the expression, so it is obvious what each metric measures. One subtlety with count: count(*) FILTER (WHERE ...) and count(CASE WHEN ... THEN 1 END) give the same number, because count never counts NULL anyway.

Useful combinations:

• Segment share: count(*) FILTER (WHERE status = 'paid')::numeric / count(*).
• Multiple thresholds: separate FILTERs for salary > 100000, salary > 200000, and so on.
• Conditional distinct: count(DISTINCT user_id) FILTER (WHERE amount > 0).

Gotcha: NULL, empty groups, and portability

A few traps that are easy to hit:

• If no rows pass the FILTER, count returns 0, but sum/avg/max return NULL, not zero. Wrap them in COALESCE when you need 0.

-- High earners per department, 0 instead of NULL when none match
SELECT
  dept,
  coalesce(sum(salary) FILTER (WHERE salary > 150000), 0) AS top_payroll
FROM employees
GROUP BY dept;

• FILTER cannot be combined with window functions using OVER in most engines — it is for aggregates only.
• Engine support:
  • PostgreSQL (9.4+), SQLite (3.30+), and DuckDB support FILTER natively.
  • MySQL/MariaDB and older versions of other databases do not understand the FILTER syntax — fall back to SUM(CASE WHEN ...).
  • ClickHouse takes its own route — combinators like countIf(cond) and sumIf(col, cond).

-- Portable fallback for MySQL and older engines
SELECT
  sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)     AS paid_orders,
  sum(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS revenue_paid
FROM orders;

Bottom line: on PostgreSQL and compatible engines, reach for FILTER — it is cleaner and faster for segmented reports. Where it is missing, keep the SUM(CASE WHEN ...) equivalent handy.