Grouping by multiple columns and ORDER BY

You can already group by a single column and filter groups. Now we will add two powerful tricks: grouping by multiple columns at once and sorting the finished report. These are what turn a raw table into a clear, ordered document, @name.

GROUP BY on several columns

You can give GROUP BY more than one column, separating them with a comma. MySQL will then create a separate group for each unique combination of those column values.

1SELECT kategoria_id, rok_wydania, COUNT(*)
2FROM ksiazki
3GROUP BY kategoria_id, rok_wydania;

This means: "For each pair (category, publication year) give the number of books". If category 1 has books from 2010 and 2020, two separate groups are formed: (1, 2010) and (1, 2020).

| kategoria_id | rok_wydania | COUNT(*) | |--------------|-------------|----------| | 1 | 2010 | 5 | | 1 | 2020 | 8 | | 2 | 2020 | 3 |

Remember the rule from the previous chamber: every column on the SELECT list that is not an aggregate must appear in GROUP BY. Here we list

kategoria_id

rok_wydania

Sorting the report: ORDER BY

A grouping result can also be sorted - with the ORDER BY clause you know from earlier chambers. Importantly: ORDER BY can also sort by an aggregate function.

1SELECT kategoria_id, COUNT(*)
2FROM ksiazki
3GROUP BY kategoria_id
4ORDER BY COUNT(*) DESC;

This means: "Show the number of books in each category, and arrange the result from the most numerous category to the least numerous". The word DESC (from descending) means descending order; ASC (or no word at all) - ascending.

The place of ORDER BY in a query

ORDER BY always stands at the very end - after GROUP BY and after HAVING:

1SELECT kategoria_id, COUNT(*)
2FROM ksiazki
3GROUP BY kategoria_id
4HAVING COUNT(*) > 3
5ORDER BY COUNT(*) DESC;

First we group, then we discard the small groups (HAVING), and finally we arrange the rest in the desired order. Grouping by multiple columns and sorting are the final brush strokes that turn numbers into a finished report!