Grouping by Multiple Columns

A group can be defined by a combination of values

GROUP BY can use several columns. In that case, rows belong to the same group only when the entire specified combination of values matches.

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

For example, products in the book category with the ACTIVE status form one group, while products in the same category with the ARCHIVED status form another.

More grouping columns mean more detail

Grouping only by category answers “how many products are in each category.” Adding status changes the question to “how many products of each status are in each category.”

SELECT country, city, AVG(total) AS average_order
FROM orders
GROUP BY country, city;

Now the average is calculated separately for each country + city pair rather than for the whole country.