GROUP BY, HAVING, FILTER and grouping sets

conceptThe concept

Every column in the SELECT list must either be inside an aggregate or appear in GROUP BY (or be functionally dependent on a grouped primary key, which PostgreSQL recognises). WHERE filters rows before grouping; HAVING filters groups after.

count(*) FILTER (WHERE status = 'cancelled') computes a conditional aggregate in the same pass, replacing sum(CASE WHEN ... THEN 1 ELSE 0 END). string_agg(x, ', ' ORDER BY x) and array_agg collect values. percentile_cont(0.5) WITHIN GROUP (ORDER BY x) gives a median.

GROUP BY ROLLUP (a, b) produces groups for (a, b), (a) and the grand total; CUBE produces every combination; GROUPING SETS lists exactly the ones you want. GROUPING(col) tells you whether a NULL is a real value or a subtotal row.

whyWhy it matters

Dashboards are aggregation. Doing it in one SQL pass, close to the data, is faster and more consistent than pulling rows into the application and summing there.

Use caseReal-world scenario

Finance wants revenue by country and tier, with a subtotal per country and a grand total, plus the share of cancelled orders. That is one query with ROLLUP and FILTER.

Try itExamples

Run and edit these in the interactive lesson.

SELECT c.country,
       count(*) AS orders,
       count(*) FILTER (WHERE o.status = 'cancelled') AS cancelled,
       round(100.0 * count(*) FILTER (WHERE o.status = 'cancelled') / count(*), 1) AS cancel_pct,
       sum(o.total) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.country
HAVING count(*) > 500
ORDER BY revenue DESC;

SELECT category,
       count(*) AS products,
       string_agg(DISTINCT attrs->>'brand', ', ' ORDER BY attrs->>'brand') AS brands,
       min(price) AS cheapest,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY price) AS median_price
FROM products
GROUP BY category
ORDER BY category;

SELECT coalesce(c.country, 'ALL') AS country,
       CASE WHEN GROUPING(c.tier) = 1 THEN '(subtotal)' ELSE c.tier END AS tier,
       count(*) AS orders,
       sum(o.total) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status <> 'cancelled'
GROUP BY ROLLUP (c.country, c.tier)
ORDER BY c.country NULLS LAST, GROUPING(c.tier), c.tier;

Grouping by c.id lets you select any other column of customers, because they depend on the key.

SELECT c.id, c.name, c.tier, count(*) AS orders, sum(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY spent DESC
LIMIT 5;

internalsUnder the hood

The planner groups with either HashAggregate (build a hash table of groups, no sort needed) or GroupAggregate (input sorted by the group keys). Since 13, HashAggregate spills to disk when it exceeds work_mem times hash_mem_multiplier instead of using unbounded memory.

Avoid / preferDo this, not that

SELECT status, count(*) FROM orders WHERE count(*) > 100 GROUP BY status;

SELECT status, count(*) FROM orders GROUP BY status HAVING count(*) > 100;

SELECT count(*) FROM orders WHERE status = 'paid';
SELECT count(*) FROM orders WHERE status = 'shipped';
SELECT count(*) FROM orders WHERE status = 'cancelled';

SELECT count(*) FILTER (WHERE status = 'paid') AS paid,
       count(*) FILTER (WHERE status = 'shipped') AS shipped,
       count(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders;

PitfallsCommon mistakes

• Putting a condition on an aggregate in WHERE (WHERE count(*) > 5). Aggregates belong in HAVING.
• Dividing two integer counts and getting 0. Multiply by 100.0 or cast to numeric first.
• Confusing a NULL group key with a ROLLUP subtotal row. Use GROUPING() to tell them apart.

LimitsWhen not to use it

Do not run heavy GROUP BY queries on every page view of a dashboard. Precompute with a materialized view or summary table and refresh on a schedule.

SecuritySecurity notes

Aggregates over very small groups can identify individuals ("average salary of the one employee in Finance in Munich"). When exporting or sharing aggregates, suppress groups below a minimum size.

ChallengeTry it yourself

SELECT country,
       count(*) AS customers,
       count(*) FILTER (WHERE tier IN ('gold', 'platinum')) AS gold_plus
FROM customers
GROUP BY country
HAVING count(*) > 300
ORDER BY customers DESC;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Aggregate functions
• GROUPING SETS, CUBE and ROLLUP