Summarising Data: Aggregates & GROUP BY

Aggregate functions turn many rows into one value: count(*), sum, avg, min, max. Use round(avg(score), 1) for readable averages.

GROUP BY computes aggregates per group — per subject, per form, per term. Every selected column must either be in GROUP BY or inside an aggregate.

WHERE filters rows before grouping; HAVING filters groups after aggregating. To show names instead of ids, JOIN the related table — joins are covered fully in the Intermediate track.

aggregates.sqlSQL

SELECT count(*) AS students, sum(fee_balance) AS total_owed, max(fee_balance) AS largest
FROM students;

SELECT form, count(*) AS students
FROM students
GROUP BY form
ORDER BY form;

SELECT subject_id,
       count(*)              AS entries,
       round(avg(score), 1)  AS average,
       min(score)            AS lowest,
       max(score)            AS highest
FROM results
GROUP BY subject_id
ORDER BY subject_id;

-- Students averaging 60 or more, with names via JOIN
SELECT s.full_name, round(avg(r.score), 1) AS average
FROM results r
JOIN students s ON s.id = r.student_id
GROUP BY s.full_name
HAVING avg(r.score) >= 60
ORDER BY average DESC;

-- Count of passes (score >= 30) per subject using FILTER
SELECT sub.name,
       count(*) FILTER (WHERE r.score >= 30) AS passed,
       count(*)                              AS sat
FROM results r
JOIN subjects sub ON sub.id = r.subject_id
GROUP BY sub.name
ORDER BY sub.name;

Runs in your browser · PostgreSQL

Key points

Aggregates collapse rows; GROUP BY does it per group.
WHERE filters rows before grouping, HAVING filters groups after.
count(*) FILTER (WHERE ...) counts subsets in one pass.

Exercise

Find: the average fee balance per form; the number of students per gender; subjects where the average score is below 65; and each student's best score (show their name using a JOIN).

Show solution

Try the exercise yourself first — then compare your approach with this one.

GROUP BY gives one row per form or gender. HAVING filters subjects after averaging. The best score per student needs a JOIN to get names, then max(score) per student.

aggregates.sqlSQL

-- Average fee balance per form
SELECT form, round(avg(fee_balance), 2) AS avg_balance
FROM students
GROUP BY form
ORDER BY form;

-- Students per gender
SELECT gender, count(*) AS students
FROM students
GROUP BY gender;

-- Subjects whose average is below 65
SELECT sub.name, round(avg(r.score), 1) AS average
FROM results r
JOIN subjects sub ON sub.id = r.subject_id
GROUP BY sub.name
HAVING avg(r.score) < 65;

-- Each student's best score
SELECT s.full_name, max(r.score) AS best_score
FROM results r
JOIN students s ON s.id = r.student_id
GROUP BY s.full_name
ORDER BY best_score DESC;

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding