Aggregation & GROUP BY

the one rule

Once a query has GROUP BY, every column in SELECT must be either (a) an aggregate function, or (b) a column listed in GROUP BY. Anything else is ambiguous — which row's value would it even show, out of a whole group? PostgreSQL enforces this at query-parse time; some engines (older MySQL) used to silently pick an arbitrary row instead, which is worse.

SELECT category, COUNT(*), AVG(price)
FROM products
GROUP BY category;
-- fine: category is grouped, COUNT/AVG are aggregates

SELECT category, name, COUNT(*)
FROM products
GROUP BY category;
-- error: name is neither aggregated nor grouped

HAVING vs. WHERE

WHERE filters rows before grouping happens; HAVING filters groups after aggregation. This is why you can't write WHERE COUNT(*) > 5 — at the point WHERE runs, no aggregate has been computed yet.

SELECT category, COUNT(*) AS n
FROM products
WHERE price > 0            -- row-level filter, applied first
GROUP BY category
HAVING COUNT(*) > 5;       -- group-level filter, applied after grouping

the standard aggregate functions

Function	Notes
`COUNT(*)`	counts rows, including ones with NULL columns
`COUNT(col)`	counts non-NULL values of `col` only
`COUNT(DISTINCT col)`	counts distinct non-NULL values
`SUM`, `AVG`, `MIN`, `MAX`	ignore NULLs automatically
`ARRAY_AGG(col)`	collects values into an array
`STRING_AGG(col, sep)`	concatenates values with a separator

GROUPING SETS, ROLLUP, and CUBE

Sometimes you need several levels of summary in one result — total by (region, product), total by region alone, and a grand total. Doing that with three separate queries and a UNION works but is verbose; PostgreSQL can do it in one pass.

SELECT region, product, SUM(amount)
FROM sales
GROUP BY ROLLUP (region, product);
-- produces: (region, product) subtotals, then (region, NULL) subtotals per region, then a (NULL, NULL) grand total

ROLLUP produces a hierarchical set of subtotals; CUBE produces every combination (not just hierarchical ones); GROUPING SETS lets you specify exactly which combinations you want. Worth knowing these exist — reach for them before hand-rolling a UNION ALL of near-identical queries.

where to go from here

Window Functions — when you need per-row detail and an aggregate, not a collapsed summary.
Joins Explained — the fan-out pitfall that silently corrupts aggregates computed after a join.