SQL and MySQL course Β· Module 6: Aggregation and grouping
GROUP BY - grouping data
In this lesson3
So far we have counted all books at once. But what if we want to count them separately for each category? Here enters the most powerful spell of this chamber - GROUP BY.
What is grouping?
GROUP BY splits rows into groups that share the same value in a chosen column, and then lets you compute an aggregate for each group separately.
Imagine taking all the scrolls from a shelf and arranging them into separate piles - one pile per category. Then you count each pile on its own.
SELECT kategoria_id, COUNT(*)
FROM ksiazki
GROUP BY kategoria_id;This means: "For each category give its number and the number of books". The result is one row per category:
| kategoria_id | COUNT(*) |
|---|---|
| 1 | 42 |
| 2 | 18 |
| 3 | 90 |
The rule: SELECT and GROUP BY
This is an important rule: only the following may appear in the SELECT list:
- columns listed in GROUP BY (here:
kategoria_id), - and aggregate functions (here:
COUNT(*)).
Why? Because each result row represents a whole group. The question "what is the title of this group" makes no sense - a group contains many titles.
Other grouping examples
-- Average book price in each category
SELECT kategoria_id, AVG(cena)
FROM ksiazki
GROUP BY kategoria_id;-- How many authors come from each country
SELECT kraj, COUNT(*)
FROM autorzy
GROUP BY kraj;GROUP BY turns one huge shelf into a clear report. It is the heart of data analysis!
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 is the GROUP BY clause used for?
2. Which columns may appear in the SELECT list when using GROUP BY?
These are 2 of 3 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Code editor
Write a query that, for each category (the kategoria_id column), counts the number of books. Select the kategoria_id column and COUNT(*), and group the result by kategoria_id.
- Vertical ordering
Arrange the clauses of a simple grouping query in the correct order (top to bottom):
- Horizontal ordering
Arrange the tokens on a single line to form the SELECT list that counts authors grouped by country:
- Click in order
Click the tokens in order to build a query that computes the average book price in each category:
- Horizontal ordering
Arrange the tokens on a single line to form a clause that groups by category: