GROUP BY

Grouping rows, the SELECT rule, and grouping by several columns.

What you'll be able to do

Write correct GROUP BY queries
Apply the every-column rule
Group by multiple columns

The basic form

SELECT region, SUM(total) AS revenue
FROM orders
GROUP BY region;

One output row per distinct region, with the aggregate computed within each.

The rule

Every column in SELECT must either appear in GROUP BY or be inside an aggregate.

-- invalid
SELECT region, city, SUM(total) FROM orders GROUP BY region;

city is neither grouped nor aggregated, so the database cannot know which city to show for a region containing several.

Postgres and SQL Server reject this. MySQL historically allowed it and returned an arbitrary value — worse than an error, because it looks like it worked.

Grouping by several columns

SELECT region, city, SUM(total) AS revenue
FROM orders
GROUP BY region, city;

One row per unique combination. Adding columns makes groups finer and increases the row count.

Grouping by an expression

SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(total)
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Most dialects require repeating the expression in GROUP BY. Postgres and MySQL also accept the alias or the position — GROUP BY 1 — but repeating it is the portable form.

Key points

Every SELECT column must be grouped or aggregated
More GROUP BY columns means finer groups and more rows
Repeat the expression in GROUP BY rather than relying on alias support

Check yourself