Aggregate Functions — COUNT, SUM, AVG, MIN, MAX

9 min read

Summarising Data with Aggregate Functions

Aggregate functions collapse many rows into a single summary value. They are essential for reporting, dashboards, and any kind of data analysis.

COUNT(expr) — number of non-NULL values; COUNT(*) counts all rows
SUM(expr) — total of a numeric column
AVG(expr) — arithmetic mean of a numeric column
MIN(expr) — smallest value
MAX(expr) — largest value

sql

-- Total number of students
SELECT COUNT(*) AS total_students FROM students;

-- How many students have a score recorded
SELECT COUNT(score) AS scored_count FROM enrolments;

-- Revenue from all courses
SELECT SUM(price) AS total_revenue FROM courses;

-- Average student age
SELECT AVG(age) AS avg_age FROM students;

-- Cheapest and most expensive course
SELECT MIN(price) AS cheapest, MAX(price) AS priciest FROM courses;

Aggregate functions ignore NULL values except for COUNT(*). This means AVG(score) gives the average of scores that actually exist, not assuming missing scores are zero.

Ready to test yourself?

Practice SQL— quiz & coding exercises