Aggregates in depth: FILTER, ordered aggregates and ROLLUP

GROUP BY collapses rows into groups and an aggregate turns each group into one value. Two rules decide where a condition goes: WHERE filters rows before grouping and cannot mention an aggregate; HAVING filters groups after aggregation and usually does.

FILTER: one aggregate, its own WHERE

count(*) FILTER (WHERE outcome = 'failed') counts only the rows that pass the filter, inside the same group. It is the PostgreSQL (and SQL standard) replacement for sum(CASE WHEN ... THEN 1 ELSE 0 END), and it works with every aggregate. Ordered aggregates take their own ORDER BY inside the parentheses, independent of the query's: string_agg(x, ',' ORDER BY x). bool_and / bool_or answer "do all / any rows in the group satisfy this?".

1-- outcome is NULL while a deployment is still running; minutes is NULL when nobody timed it.2CREATE TABLE deployments (id int PRIMARY KEY, service text NOT NULL, outcome text, minutes int);3INSERT INTO deployments VALUES4  (1, 'web', 'failed', 3), (2, 'web', 'success', 5), (3, 'web', 'success', NULL),5  (4, 'api', 'failed', 2), (5, 'api', NULL, 1), (6, 'ops', 'success', 8);

1SELECT service,2       count(*)                                   AS total,3       count(*) FILTER (WHERE outcome = 'failed')  AS failed,4       count(*) FILTER (WHERE outcome = 'success') AS succeeded,5       string_agg(id::text, ',' ORDER BY id DESC) AS ids_newest_first,6       bool_and(minutes IS NOT NULL)              AS all_timed7FROM deployments8GROUP BY service9HAVING count(*) FILTER (WHERE outcome = 'failed') > 010ORDER BY service;

Note what HAVING did: service ops has no failed deployment, so its group was removed after counting. And the api deployment that is still running (NULL outcome) counts in total but in neither filtered column, because NULL = 'failed' is not true.

Ordered-set aggregates: medians and modes

percentile_cont(0.5) WITHIN GROUP (ORDER BY v) interpolates between the two middle values; percentile_disc(0.5) returns an actual value from the set (the first one whose position reaches the fraction). mode() returns the most frequent value; when several values tie, which one you get is not defined. avg is skewed by outliers; the median is not.

1CREATE TABLE response_ms (v int NOT NULL);2INSERT INTO response_ms VALUES (1), (2), (10), (40);

1SELECT avg(v)                                          AS mean,2       percentile_cont(0.5) WITHIN GROUP (ORDER BY v)  AS median_cont,3       percentile_disc(0.5) WITHIN GROUP (ORDER BY v)  AS median_disc,4       mode() WITHIN GROUP (ORDER BY v)                AS most_common5FROM response_ms;

ROLLUP, CUBE and GROUPING SETS

GROUP BY ROLLUP (a, b) computes, in one query, the groups (a, b), (a) and (): detail rows, a subtotal per a and a grand total. CUBE adds every combination; GROUPING SETS ((a), (b), ()) lists exactly the ones you want.

In a subtotal row the rolled-up column is NULL, which collides with data that is genuinely NULL. GROUPING(col) returns 1 when col was rolled up in this row and 0 when it is a real value. With several arguments it returns a bit mask (first argument is the high bit).

1CREATE TABLE shipments (depot text, carrier text NOT NULL, parcels int NOT NULL);2INSERT INTO shipments VALUES3  ('north', 'post', 10), ('north', 'courier', 5), ('south', 'post', 7), (NULL, 'post', 2);

1SELECT depot, carrier, sum(parcels) AS total,2       GROUPING(depot, carrier) AS rolled_up3FROM shipments4GROUP BY ROLLUP (depot, carrier)5ORDER BY GROUPING(depot, carrier), depot NULLS LAST, carrier;

Look at the rows with a NULL depot: (NULL, post, 2) and (NULL, NULL, 2) are the parcels that never had a depot and their subtotal, while (NULL, NULL, 24) is the grand total. Only GROUPING tells them apart.

Common mistakes

• Putting count(*) > 1 in WHERE; aggregates are not allowed there. Conversely, a condition on a plain grouped column is accepted in HAVING (the planner can often move it into WHERE for you), but WHERE is where it says what you mean.
• Labelling ROLLUP totals with COALESCE(region, 'Total'), which also relabels the group of rows whose region really is NULL.
• Expecting string_agg to follow the outer ORDER BY; it needs its own.
• Reporting avg as "typical" for skewed data where the median is far lower.

What interviewers ask

• WHERE versus HAVING? Rows before grouping versus groups after; aggregates only in HAVING.
• How would you count failed and successful deployments per service in one query? count(*) FILTER (WHERE ...) per outcome (or elsewhere).