GROUP BY

Aggregates give one number for the whole table. GROUP BY gives one number per group: per category, per country, per month.

One row per group

SELECT category, COUNT(*) AS products
FROM products
GROUP BY category;

The database sorts the rows into groups by category, then runs COUNT(*) on each group.

Several aggregates per group

SELECT
  category,
  COUNT(*)              AS products,
  ROUND(AVG(price), 2)  AS avg_price,
  SUM(stock)            AS units
FROM products
GROUP BY category
ORDER BY avg_price DESC;

The golden rule

Every column in the SELECT must either be in the GROUP BY or inside an aggregate. This works because department is grouped and everything else is aggregated:

SELECT department, COUNT(*) AS staff, MAX(salary) AS top_salary
FROM employees
GROUP BY department;

Grouping with a filter

WHERE filters rows before they are grouped:

SELECT country, COUNT(*) AS customers
FROM customers
WHERE joined IS NOT NULL
GROUP BY country
ORDER BY customers DESC, country;

Grouping by several columns

SELECT customer_id, status, COUNT(*) AS orders
FROM orders
GROUP BY customer_id, status
ORDER BY customer_id;

Each combination of customer and status becomes its own group.

Grouping by a calculation

SELECT SUBSTR(order_date, 1, 7) AS month, COUNT(*) AS orders
FROM orders
GROUP BY month;

SUBSTR(order_date, 1, 7) takes YYYY-MM from the date, so orders are counted per month.