GROUP BY

GROUP BY splits rows into groups that share a value, then calculates aggregates for each group: revenue per month, employees per department, average price per category. It turns a list of facts into a summary table.

Grouping by one column

SELECT category, COUNT(*) AS products, round(AVG(price), 2) AS avg_price
FROM products
GROUP BY category;

Each distinct category becomes one row, and the aggregates are calculated per group.

How it works

1. FROM and WHERE pick the rows.
2. GROUP BY sorts them into buckets by the grouping value.
3. Each bucket becomes one output row; aggregates summarise the bucket.

The rule that follows: every column in the select list must either be in GROUP BY or be inside an aggregate. Otherwise there's no single value to show for the group.

Grouping with WHERE and ORDER BY

SELECT status, COUNT(*) AS orders
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY status
ORDER BY orders DESC;

Grouping by several columns

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

A group is formed for each distinct combination. NULLs are grouped together, so Farah gets a France / NULL group.

Grouping by an expression

SELECT strftime('%Y', hire_date) AS hire_year, COUNT(*) AS hires
FROM employees
GROUP BY hire_year
ORDER BY hire_year;

Grouping by a calculated value — a year, a price band, the first letter of a name — is common. Standard SQL requires you to repeat the expression in GROUP BY; SQLite, PostgreSQL and MySQL also accept the alias.

SELECT CASE WHEN price < 20 THEN 'under 20'
            WHEN price < 50 THEN '20–49'
            ELSE '50+' END AS band,
       COUNT(*) AS products
FROM products
GROUP BY band
ORDER BY MIN(price);

Revenue per order

SELECT order_id,
       COUNT(*) AS lines,
       SUM(quantity) AS units,
       round(SUM(quantity * unit_price), 2) AS order_total
FROM order_items
GROUP BY order_id
ORDER BY order_total DESC
LIMIT 5;

Monthly report

SELECT strftime('%Y-%m', o.order_date) AS month,
       COUNT(DISTINCT o.id) AS orders,
       round(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY month
ORDER BY month;

This preview uses a join to combine orders with their items — joins are explained from lesson 23. Notice COUNT(DISTINCT o.id): after the join, an order with two items appears twice.

Try it yourself

Show each department's number of employees, total payroll and highest salary.