SQL Aggregate Functions

A normal query returns one result row for every row it reads. An aggregate function works differently. It reads many rows and condenses them into a single answer, such as a total, average, or count. This is the first step from looking at raw data to summarizing it.

There are five functions you will use constantly.

A Class Result Example

Take an exam_scores table. Dev missed the test, so his score is empty (NULL).

SELECT COUNT(*)        AS total_rows,
       COUNT(score)    AS scores_recorded,
       SUM(score)      AS total_marks,
       ROUND(AVG(score), 1) AS average,
       MIN(score)      AS lowest,
       MAX(score)      AS highest
FROM exam_scores;

Output:

Code Explanation:

• COUNT(*) counts every row in the table, so Dev is included, and the answer is 6.
• COUNT(score) counts only the rows where score has a value, so Dev is skipped and the answer is 5.
• SUM adds the five real scores to get 411.
• AVG divides 411 by 5, not by 6, and ROUND(..., 1) trims it to one decimal place.
• MIN and MAX pick the lowest and highest scores.

Why NULL Matters Here

Every aggregate function except COUNT(*) ignores NULL values. That is why the average above is 82.2, not 68.5, which you would get if you treated Dev’s missing score as zero.

Whenever a count or average looks off in a real dataset, check for empty values first. It is one of the most common reasons for an incorrect result.