GROUP BY

Groups rows that share values so aggregates are computed per group — one output row per group.

Syntax

SELECT key1, key2, aggregate(...)
FROM ...
[WHERE ...]
GROUP BY key1, key2
[HAVING ...]

GROUP BY ROLLUP (a, b) | CUBE (a, b) | GROUPING SETS ((a, b), (a), ())

GROUP BY puts rows with equal key values into one bucket; each bucket becomes one output row.

Every selected column must either be in GROUP BY or be inside an aggregate. Exception: if you group by a table's primary key, other columns of that table may be selected (they are functionally dependent).
You can group by expressions (date_trunc('month', ordered_at)), and PostgreSQL also accepts a select-list alias or position (GROUP BY 1).
NULL: all NULL keys form one group.
ROLLUP (a, b) adds subtotals per a and a grand total; CUBE adds every combination; GROUPING SETS lists exactly the groupings you want. Subtotal rows have NULL in the rolled-up key; GROUPING(col) returns 1 on those rows, to tell them apart from real NULLs.
Group at the right grain: when joining one-to-many tables, aggregate the many-side first to avoid fan-out.

Examples

Count and total per group starter

SELECT status, count(*) AS orders, sum(quantity) AS units
FROM orders
GROUP BY status
ORDER BY orders DESC, status;

One row per distinct status.

Group by an expression shop

SELECT date_trunc('month', ordered_at)::date AS month, channel, count(*) AS orders
FROM orders
WHERE ordered_at >= '2025-01-01' AND ordered_at < '2025-04-01'
GROUP BY month, channel
ORDER BY month, channel;

One row per (month, channel) pair.

Subtotals with ROLLUP starter

SELECT COALESCE(category, 'ALL') AS category, COALESCE(status, 'all statuses') AS status,
       sum(o.quantity) AS units, GROUPING(category, status) AS level
FROM orders o
JOIN products p USING (product_id)
GROUP BY ROLLUP (category, status)
ORDER BY category NULLS LAST, status NULLS LAST;

Detail rows (level 0), a subtotal per category (level 1) and a grand total (level 3).

Pitfalls

column must appear in the GROUP BY clause or be used in an aggregate function: add it to GROUP BY or aggregate it.
Grouping by a name instead of an id when names are not unique (two customers named the same merge).
Grouping by too many columns (e.g. including the id) so every row is its own group.
Filtering groups in WHERE instead of HAVING.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

MySQL: ROLLUP is written GROUP BY a, b WITH ROLLUP; ONLY_FULL_GROUP_BY (on by default) enforces the same rule as PostgreSQL.
SQL Server: Cannot GROUP BY a select alias or position; repeat the expression.
BigQuery: Allows GROUP BY alias and position; supports ROLLUP, CUBE and GROUPING SETS.