Summarizing and grouping · Lesson 6 of 20

GROUP BY and HAVING

One number per group, and filtering the groups themselves.

10 minutes2 games

COUNT(*) alone gives one number for the whole table. To answer "how many students in each city?" you need GROUP BY: it splits rows into groups by a column's value, and the aggregate runs on each group separately.

SQL

SELECT city, COUNT(*) AS students, MAX(xp) AS best_xp
FROM students
GROUP BY city
ORDER BY students DESC, city;

8 rows

Split: Cairo (3), Alexandria (2), Giza (2), Mansoura (1)

COUNT and MAX per group

4 rows in the result

HAVING: filtering after grouping

WHERE filters rows before grouping, so it can't use COUNT. To filter the groups themselves (cities with more than one student) use HAVING.

Step by step1 / 6

1SELECT city, COUNT(*) AS students
▶FROM students
3WHERE xp IS NOT NULL
4GROUP BY city
5HAVING COUNT(*) > 1
6ORDER BY city;

Variables

rows

Output

…

Take every student.

The real execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

Spot the bug

This query raises an error. Which line?

Exercise

For each track in courses: the number of courses as courses and the average price as avg_price, ordered by track.

Expected result:

track | courses | avg_price
data | 2 | 350
systems | 1 | 500
web | 3 | 350

SQL

SELECT track
FROM courses;

Learn more from

Played 0 of 2 games

SQL