GROUP BY

GROUP BY creates a separate summary for each group

Without grouping, an aggregate function calculates one summary for the whole result. GROUP BY divides rows into groups with equal values and runs aggregation separately for each group.

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

If the table contains orders with the statuses NEW, PAID, and CANCELLED, the result contains one row for each status and the number of orders in that group.

What can be selected next to an aggregate

After grouping, a column in SELECT should either define the group or be used inside an aggregate function. Otherwise, it is unclear which value from the multiple rows in the group should be returned.

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

Here, category defines the group, while AVG(price) calculates one value for the rows inside that group.