Lesson 7 of 17 · Summarising Data
GROUP BY
Summarise data per group with SQL GROUP BY. Count, sum and average for each category, group by several columns and avoid the classic GROUP BY error.
- 7 min read
- Beginner
- Reviewed
- How to calculate one summary for each group of rows
- The rule that decides which columns you may select
- How to group by more than one column
- How to sort groups by their summary
One answer per group
In the last lesson, an aggregate turned the whole table into a single number: the number of students. But often you want that number for each of something. How many students per country? How many courses per category? The average score for each course?
GROUP BY splits the rows into groups and calculates the aggregate once for each group.
SELECT country, COUNT(*) AS students FROM students GROUP BY country;
How GROUP BY works
Picture it in three steps.
- Split. The database puts rows with the same country into the same pile.
- Summarise. It runs
COUNT(*)on each pile separately. - Combine. It returns one row per pile.
Every GROUP BY query follows this pattern, whatever the aggregate.
SELECT category,
COUNT(*) AS courses,
SUM(hours) AS total_hours
FROM courses
GROUP BY category;Any aggregate works
Here is the average score for each course. The enrollments table identifies a course by its number, course_id. You will learn to show the course title instead in the module on combining tables.
SELECT course_id,
COUNT(*) AS enrolled,
ROUND(AVG(score), 1) AS average_score
FROM enrollments
GROUP BY course_id;The golden rule of GROUP BY
Every column in SELECT must be one of two things:
- a column listed in
GROUP BY, or - inside an aggregate function.
Why? Each group becomes one row. country has one value per group, so it is fine. COUNT(*) produces one value per group, so it is fine. But name has many different values inside a group. There is no single name to show.
Most database systems refuse such a query with an error. A few, including this practice database, pick one of the values for you, which is worse, because the answer looks right when it is not. Follow the rule and you will never be caught out.
Sorting groups
ORDER BY comes after GROUP BY, and it can sort by an aggregate. This shows the most popular courses first.
SELECT course_id, COUNT(*) AS enrolled FROM enrollments GROUP BY course_id ORDER BY enrolled DESC, course_id;
Filtering before grouping
WHERE removes rows before the groups are formed. This counts only finished enrollments for each course.
SELECT course_id, COUNT(*) AS finished FROM enrollments WHERE progress = 100 GROUP BY course_id ORDER BY finished DESC, course_id;
Grouping by more than one column
List several columns to make a group for every combination of values.
SELECT category, hours, COUNT(*) AS courses FROM courses GROUP BY category, hours ORDER BY category, hours;
The order of clauses
With GROUP BY added, a query is written in this order.
SELECTFROMWHEREGROUP BYORDER BYLIMIT
Common mistakes
Count how many courses there are in each category.
-- For each category, show the category and the number of courses in it. -- Name the count column "courses".
Which query is valid in every database system?
Summary
GROUP BYcalculates an aggregate once for each group of rows.- Think: split, summarise, combine.
- Every selected column must be in
GROUP BYor inside an aggregate. WHEREfilters rows before grouping.ORDER BYcan sort the groups by their aggregate.
Next: keeping only some of the groups.
Finished reading? Tick it off to track your progress.