Using FILTER (WHERE …), how do you return total, paid and refunded order counts per customer in one SELECT?

## The shape of the query The task — "one row per customer, several differently-conditioned counts" — is the canonical use of the filter clause. ```sql SELECT customer_id, COUNT(*) AS orders_total, COUNT(*) FILTER (WHERE status = 'paid') AS orders_paid, COUNT(*) FILTER (WHERE status = 'refunded')AS orders_refunded, SUM(amount) FILTER (WHERE status = 'paid') AS revenue_paid FROM orders GROUP BY customer_id; ``` Read it left to right: `GROUP BY customer_id` decides the rows of the result, and each aggregate then consumes its group's rows subject to its own condition. `orders_total` has no filter, so it counts all of the customer's orders; `orders_paid` counts only the ones whose status is `'paid'`; `revenue_paid` sums `amount` over the same subset. ## Why not several queries The pre-FILTER alternatives are all heavier to write and read. Three separate grouped queries have to be joined back together on `customer_id`, and the join has to be an outer join or customers missing from one result vanish. Three correlated scalar subqueries in the SELECT list repeat the table reference three times. Conditional aggregation with `CASE` is a single pass like `FILTER`, but each measure becomes a nested expression the reader has to decode. The filter clause states the condition next to the aggregate it belongs to, which is why it reads well in reporting queries with a dozen measures. ## Mixing aggregate kinds Any aggregate can carry a filter clause, and they can differ within one SELECT list: ```sql SELECT COUNT(DISTINCT customer_id) FILTER (WHERE status = 'paid') AS paying_customers, AVG(amount) FILTER (WHERE status = 'paid') AS avg_paid_order, MAX(created_at) FILTER (WHERE status = 'refunded') AS last_refund FROM orders; ``` `DISTINCT` stays inside the parentheses with the argument; the filter clause goes after the closing parenthesis. ## Naming and result columns Always alias a filtered aggregate. Engines derive a default column name from the expression, and for a filtered aggregate that name is unhelpful or engine-specific; downstream code that reads by column name breaks. Aliases also document the condition in the result set, which is the point of the query. ## What each measure returns when nothing matches If a customer has no refunds, their `orders_refunded` is `0` — `COUNT` over an empty input is zero. But `SUM`, `AVG`, `MIN` and `MAX` over an empty input return NULL, so a customer with no paid orders gets `NULL` for `revenue_paid`, not `0`. Wrap it in `COALESCE(SUM(amount) FILTER (WHERE status = 'paid'), 0)` if the consumer wants a number. The customer's row itself is still present either way: grouping was decided before any filter clause ran. ## Watch the predicate's logic The filter predicate is evaluated with the usual three-valued logic; only TRUE admits a row. If `status` can be NULL, neither `status = 'paid'` nor `status <> 'paid'` admits those rows, so `orders_paid + orders_other` may be less than `orders_total`. Use `status IS DISTINCT FROM 'paid'` or an explicit `status IS NULL` measure when NULLs are meaningful. ## Portability `FILTER (WHERE …)` is standard SQL and is accepted by PostgreSQL and SQLite. MySQL, SQL Server and Oracle reject it; there you write the same query with `CASE` inside each aggregate: ```sql SELECT customer_id, COUNT(*) AS orders_total, COUNT(CASE WHEN status = 'paid' THEN 1 END) AS orders_paid, COUNT(CASE WHEN status = 'refunded' THEN 1 END) AS orders_refunded FROM orders GROUP BY customer_id; ``` Same single pass, same results, more punctuation. ## Interview framing Interviewers use this as a keyboard question: they describe a small report — orders by state, users by signup channel, tickets by priority — and watch whether you reach for one grouped query or for three. Producing the single-pass version, aliasing the columns, and noting the 0-versus-NULL difference between the count and the sum is a complete answer.