Grouping 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, MAX(score) AS best, MIN(score) AS lowest
FROM student
GROUP BY form;
form | best | lowest
11A | 95 | 88
11B | 72 | 64
The five rows split into two groups: 11A (Mei 88, Sara 95, Lin 88) and 11B (Jamal 72, Tom 64). Each group becomes one result row, and MAX and MIN work inside each group. COUNT(*), SUM and AVG work the same way.
WHERE filters rows before grouping. To filter the groups themselves — using an aggregate — use HAVING:
SELECT form, ROUND(AVG(score), 2) AS avg_score
FROM student
GROUP BY form
HAVING AVG(score) >= 80;
form | avg_score
11A | 90.33
11B's average is 68, so that group is removed. WHERE AVG(score) >= 80 is an error: when WHERE runs, the groups do not exist yet.
GROUP BY works on any column. This finds scores that more than one student got:
SELECT score, COUNT(*) AS n
FROM student
GROUP BY score
HAVING COUNT(*) > 1;
score | n
88 | 2
Question. For each form, show the form and the total of the scores of the students who scored 70 or more, as total.
Answer. WHERE removes rows before they are grouped, so Tom (64) never reaches the 11B group:
SELECT form, SUM(score) AS total
FROM student
WHERE score >= 70
GROUP BY form;
form | total
11A | 271
11B | 72
The clauses always go in this order: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY ….
- Filter groups with
HAVING, rows withWHERE.HAVING COUNT(*) >= 3tests a group;WHERE score >= 70tests a row. - Clause order.
GROUP BYbeforeWHEREis a syntax error. - A plain column beside an aggregate must be in
GROUP BY. InSELECT form, name, COUNT(*) … GROUP BY form,namehas many values in each group, so the answer is meaningless. Most databases refuse the query; SQLite, which runs this playground, quietly picks one name per group, so no error does not mean it is right.
Độ khó 3/5 · 200 xu + phần thưởng nhỏ khi hoàn thành lần đầu tiên hôm nay
Phê bình mỗi form, show the form, the number of students as n, and the average score (2 d.p.) as avg_score. Group by form.
Nhấn Chạy để xem kết quả ở đây.
Độ khó 3/5 · 200 xu + phần thưởng nhỏ khi hoàn thành lần đầu tiên hôm nay
Show each form and its student count n, but only for forms with 3 or more students. Use HAVING.
Nhấn Chạy để xem kết quả ở đây.
Độ khó 3/5 · 200 xu + phần thưởng nhỏ khi hoàn thành lần đầu tiên hôm nay
For each form, count only the students who scored 80 or more, as n. The starter counts everyone: add a WHERE. (A form with no such student drops out of the result.)
Nhấn Chạy để xem kết quả ở đây.