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, 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.

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.)

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 with WHERE. HAVING COUNT(*) >= 3 tests a group; WHERE score >= 70 tests a row.
• Clause order. GROUP BY before WHERE is a syntax error.
• A plain column beside an aggregate must be in GROUP BY. In SELECT form, name, COUNT(*) … GROUP BY form, name has 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.