SQL Aggregate Functions

Aggregate functions take many rows and return one value, such as a count, a total or an average.

The Five Main Functions

COUNT

COUNT(*) counts all rows:

SELECT COUNT(*) AS total_students
FROM students;

COUNT(column) counts only the rows where that column is not NULL:

SELECT COUNT(course_id) AS with_course
FROM students;

The answer is 5, not 6, because Hamza's course_id is NULL.

MIN and MAX

SELECT MIN(marks) AS lowest, MAX(marks) AS highest
FROM students;

MIN and MAX also work on text (alphabetical order) and on dates (earliest and latest).

SUM and AVG

SELECT SUM(marks) AS total, AVG(marks) AS average
FROM students;

All the marks add up to 477, and 477 divided by 6 students is 79.5. Some databases show more decimal places, like 79.5000.

Aggregates with WHERE

WHERE filters the rows first, then the function runs on what is left. This finds the average marks of students from Karachi, rounded to 2 decimal places:

SELECT ROUND(AVG(marks), 2) AS karachi_average
FROM students
WHERE city = 'Karachi';

Ali (78), Bilal (64) and Hamza (70) add up to 212, and 212 / 3 is about 70.67.

How NULL Is Treated

Aggregate functions skip NULL values, except COUNT(*), which counts whole rows. If a student had no marks yet, AVG(marks) would be the average of the other students only, not a zero for the missing one.

The subquery version works well:

SELECT name, marks
FROM students
WHERE marks > (SELECT AVG(marks) FROM students);

These are the students who scored above the class average of 79.5.

Exercise