Grouping with GROUP BY
Grouping rows with GROUP BY
An aggregate like AVG normally summarises the whole table into one number. GROUP BY splits the rows into groups that share a value, and gives one summary row per group:
SELECT form, COUNT(*) AS n, ROUND(AVG(score), 2) AS avg_score
FROM student
GROUP BY form;
This gives one row for each form, with that form's count and average.
Filtering groups with HAVING
WHERE filters rows before grouping. To filter the groups themselves — using an aggregate — use HAVING:
SELECT form, COUNT(*) AS n
FROM student
GROUP BY form
HAVING COUNT(*) >= 3;
This keeps only forms with 3 or more students. (WHERE cannot test COUNT(*); HAVING can.)
Common mistakes
- Filter groups with
HAVING, rows withWHERE. - A plain column beside an aggregate must be in
GROUP BY.
Difficulty 3/5 · 200 coins + a small bonus on first completion today
For each form, show the form, the number of students as n, and the average score (2 d.p.) as avg_score. Group by form.
Click Run to see the output here.
Difficulty 3/5 · 200 coins + a small bonus on first completion today
Show each form and its student count n, but only for forms with 3 or more students. Use HAVING.
Click Run to see the output here.