SQL and MySQL course Β· Module 6: Aggregation and grouping
Grouping by multiple columns and ORDER BY
In this lesson3
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.
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.
SELECT kategoria_id, rok_wydania, COUNT(*)
FROM ksiazki
GROUP 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 and rok_wydania in SELECT - and both are in GROUP BY.
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.
SELECT kategoria_id, COUNT(*)
FROM ksiazki
GROUP BY kategoria_id
ORDER 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:
SELECT kategoria_id, COUNT(*)
FROM ksiazki
GROUP BY kategoria_id
HAVING COUNT(*) > 3
ORDER 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!
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What does GROUP BY kategoria_id, rok_wydania do?
2. How do you sort a grouping result from the most numerous group to the least numerous?
These are 2 of 5 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Vertical ordering
Arrange the clauses of a full aggregating query in the correct order (top to bottom):
- Code editor
Write a query that selects kategoria_id and COUNT(*) from the ksiazki table, groups by kategoria_id, and sorts the result in descending order by COUNT(*) (ORDER BY COUNT(*) DESC).
- Vertical ordering
Arrange the clauses of a query grouping by two columns in the correct order (top to bottom):
- Horizontal ordering
Arrange the tokens on a single line to form a clause that sorts in descending order by the row count:
- Code editor
Write a query that selects kategoria_id and COUNT(*) from the ksiazki table, takes only the rows where dostepna = 1, groups by kategoria_id, keeps the groups with COUNT(*) > 5, and sorts in descending order by COUNT(*).
- Code editor
Write a query that returns COUNT(*), AVG(cena), MIN(cena) and MAX(cena) at once from the ksiazki table.
- Vertical ordering
Arrange the stages in which MySQL executes an aggregating query in order (from first to last):
- Click in order
Click the clauses in the order in which MySQL processes an aggregating query with sorting:
Tasks after this lesson
- QuestionGrouping by multiple columns
- QuestionSorting by an aggregate
- Put in orderClauses of a full aggregating query
- Coding taskCategories sorted by book count
- Put in orderGrouping by two columns
- Put in orderSorting descending by row count
- Coding taskThe full song of aggregation
- QuestionFunction computing the average
- QuestionFull clause order
- Coding taskFour numbers at once
- Put in orderStages of running an aggregating query
- QuestionWHERE versus HAVING - summary
- Click in orderClause processing order