Grouping with GROUP BY

One aggregate per group

We almost never want a single number for the whole table, but one per category, per customer, per month.... That is what GROUP BY is for: it splits the rows into groups based on the values of a column and applies the aggregate function to each group separately.

SELECT category, AVG(price) AS average_price
FROM products
GROUP BY category;

This returns one row per distinct category, with the average price of the products in that category.

What can go in the SELECT

This is the golden rule: when there is a GROUP BY, the SELECT can only contain:

1. The columns you group by (the ones in the GROUP BY).
2. Aggregate functions (COUNT, SUM, AVG...).

Putting a "normal" column that is not in the GROUP BY makes no sense: the group has many rows and that field may hold different values in each one.

-- Products and total stock per category, from highest to lowest stock
SELECT category, SUM(stock) AS total_stock
FROM products
GROUP BY category
ORDER BY total_stock DESC;

ORDER BY is applied after grouping, so you can sort by the aggregate itself (by its alias or by repeating the function).