FILTER clause

Overview

The standard SQL FILTER (WHERE condition) clause attaches a row filter directly to an aggregate function call, so a single query can compute several differently-scoped aggregates side by side:

Copy code
SELECT
  region,
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders,
  SUM(revenue) FILTER (WHERE channel = 'online') AS online_revenue
FROM orders
GROUP BY region;

FILTER vs CASE WHEN

Before FILTER was widely supported, the same result required wrapping the aggregate's argument in a CASE expression:

Copy code
SELECT
  region,
  SUM(CASE WHEN channel = 'online' THEN revenue ELSE NULL END) AS online_revenue
FROM orders
GROUP BY region;

FILTER is more readable and makes intent explicit — the condition governs which rows are counted or summed, not what value is substituted. It also composes cleanly with COUNT(*), where a CASE expression would need an awkward THEN 1 ELSE NULL END pattern.

DuckDB specifics

DuckDB fully supports FILTER (WHERE ...) on any aggregate function, including with GROUP BY ALL:

Copy code
SELECT
  region,
  COUNT(*) FILTER (WHERE status = 'completed') AS completed,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders
GROUP BY ALL;

DuckDB also allows FILTER on window function aggregates (e.g., SUM(x) FILTER (WHERE ...) OVER (...)), which is a more advanced combination not all SQL engines support, letting you compute a filtered running total in one pass.