Velamuvi AIAcademy

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
In this lesson you will learn
  • 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.

Try it yourselfSQL
SELECT country, COUNT(*) AS students
FROM students
GROUP BY country;

How GROUP BY works

Picture it in three steps.

  1. Split. The database puts rows with the same country into the same pile.
  2. Summarise. It runs COUNT(*) on each pile separately.
  3. Combine. It returns one row per pile.

Every GROUP BY query follows this pattern, whatever the aggregate.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. ORDER BY
  6. LIMIT

Common mistakes

Practice exercise

Count how many courses there are in each category.

Your solutionSQL
-- For each category, show the category and the number of courses in it.
-- Name the count column "courses".
Knowledge check

Which query is valid in every database system?

Summary

  • GROUP BY calculates an aggregate once for each group of rows.
  • Think: split, summarise, combine.
  • Every selected column must be in GROUP BY or inside an aggregate.
  • WHERE filters rows before grouping.
  • ORDER BY can sort the groups by their aggregate.

Next: keeping only some of the groups.

Finished reading? Tick it off to track your progress.