GROUP BY

Syntax

SELECT grouping_column, agg_function(other_column)
FROM table
GROUP BY grouping_column;

What it does

GROUP BY collapses rows into one row per distinct combination of the grouping columns, then computes aggregate functions (SUM, COUNT, AVG, MAX, …) over each group.

Loading editor…

Every selected column must be grouped or aggregated

SELECT customer_id, order_date, SUM(amount)
FROM orders
GROUP BY customer_id;
-- ERROR: order_date is neither aggregated nor grouped

The engine has one order_date per row but only one output row per customer after grouping — it doesn't know which order's date to show. Either aggregate it (MAX(order_date)) or add it to GROUP BY (which changes the grain: one row per customer and date, not per customer).

Grouping by more than one column

Loading editor…

Every extra grouping column narrows the grain. This is now one row per (customer, status) pair, not per customer.

Subtotals: ROLLUP / CUBE / GROUPING SETS

Standard SQL (Postgres, SQL Server, MySQL 8+, Oracle — not SQLite) can add subtotal rows in one query instead of a UNION of several:

SELECT customer_id, status, SUM(amount)
FROM orders
GROUP BY ROLLUP (customer_id, status);
-- adds a subtotal row per customer_id, plus one grand-total row

CUBE adds subtotals for every combination; GROUPING SETS lets you specify exactly which subtotal levels you want.

Safety checklist

• define the intended aggregation grain before writing the query
• ensure every selected column is either grouped or aggregated
• check whether adding/removing a grouping column silently changed the grain