Counting and averaging

Sometimes you do not want the rows themselves, but a summary of them. Aggregate functions turn many rows into a single value:

• COUNT(*) — how many rows
• SUM(col) — the total
• AVG(col) — the average
• MIN(col) / MAX(col) — the smallest / largest

SELECT COUNT(*) FROM student;

Give the result a clear heading with AS. Averages often have long decimals, so wrap them in ROUND(value, 2) to keep 2 decimal places:

SELECT COUNT(*) AS n, ROUND(AVG(score), 2) AS avg_score
FROM student;

You can combine several aggregates in one query.

Question. How many students in form 11A scored 85 or more? Show the answer under the heading n.

Answer. WHERE keeps the matching rows first; COUNT(*) then counts what is left:

SELECT COUNT(*) AS n FROM student
WHERE form = '11A' AND score >= 85;

n
3

Mei (88), Sara (95) and Lin (88) pass both tests. SUM works the same way: SELECT SUM(score) FROM student WHERE form = '11B' adds up only the 11B scores.

• "How many" is COUNT; "total" is SUM. COUNT(score) counts the scores that are not empty (5 here); SUM(score) adds them up (407).
• An aggregate cannot go in WHERE. WHERE AVG(score) > 80 fails with misuse of aggregate function. Filter rows with WHERE and groups with HAVING (lesson 8).
• Round an average with ROUND(x, 2), or it may show a long decimal.