SQSQL · Lesson 4 of 8

COUNT, SUM & GROUP BY

So far every query returned rows as-is. Aggregate functions collapse many rows into one answer: how many students? What's the average grade? GROUP BY asks that per category.

SQL

-- Aggregates over the whole table:
SELECT COUNT(*) FROM students;          -- number of rows
SELECT AVG(grade) FROM students;        -- average
SELECT MAX(grade), MIN(grade) FROM students;
SELECT SUM(grade) FROM students;

-- COUNT(column) skips NULLs; COUNT(*) counts all rows:
SELECT COUNT(email) FROM students;

GROUP BY splits rows into buckets and runs the aggregate per bucket. 'Average grade per age' means: bucket rows by age, then AVG each bucket. Every selected column must either be in the GROUP BY or wrapped in an aggregate — otherwise the database can't know which row's value to show.

SQL

-- Average grade per age group:
SELECT age, AVG(grade) AS avg_grade, COUNT(*) AS n
FROM students
GROUP BY age;

-- Filter groups with HAVING (WHERE filters rows, HAVING filters groups):
SELECT age, AVG(grade) AS avg_grade
FROM students
GROUP BY age
HAVING AVG(grade) > 85;

-- Full order of clauses:
-- SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT

✦ Tip
Remember the execution order: WHERE runs before grouping (it can't see aggregates), HAVING runs after (it can). If you catch yourself writing WHERE AVG(grade) > 85, you want HAVING.