SQL GROUP BY

GROUP BY bundles rows that share the same value(s) so aggregate functions can summarise each bundle separately.

Anatomy of a grouped query

SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
ORDER BY customers DESC;

The "every column rule"

Every non-aggregated column in the SELECT list must appear in GROUP BY. This is enforced strictly in PostgreSQL and SQL Server, loosely in MySQL (with ONLY_FULL_GROUP_BY off — but that's a footgun that returns arbitrary values).

Grouping on multiple columns

SELECT country, city, COUNT(*) AS customers
FROM customers
GROUP BY country, city
ORDER BY country, customers DESC;

Where each clause fires

Example

SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country;

Exercise

Test yourself