SQL aggregate functions perform a calculation on multiple rows and return one summarized value. The functions required here are AVG (average), COUNT, MAX, MIN, and SUM; although the concept is called AVERAGE, standard SQL normally uses AVG().
In A3.3 Database programming, aggregate functions allow data to be analysed without retrieving and processing every record separately. They are placed around a field name in a SELECT statement.
| Function | Purpose | Example result |
|---|---|---|
AVG(field) | Calculates the arithmetic mean of non-NULL values | Average examination score |
COUNT(field) | Counts rows in which the specified field is not NULL | Number of recorded scores |
COUNT(*) | Counts all selected rows | Number of students |
MAX(field) | Returns the greatest value | Highest score |
MIN(field) | Returns the smallest value | Lowest score |
SUM(field) | Adds numerical values | Total of all scores |
For a table called Student containing scores of 60, 70, and 80:
SELECT AVG(score), COUNT(*), MAX(score), MIN(score), SUM(score)
FROM Student;
The results are 70, 3, 80, 60, and 210 respectively.
Aggregate functions can also summarize separate categories using GROUP BY:
SELECT classID, AVG(score)
FROM Student
GROUP BY classID;
This returns one average for each class rather than one average for the whole table. HAVING filters groups after aggregation, whereas WHERE filters individual rows before aggregation.
A common misconception is that COUNT(field) and COUNT(*) are always equivalent. They differ when the selected field contains NULL: COUNT(field) ignores that row, while COUNT(*) still counts it.
For an IB exam response, identify the correct function, write syntactically valid SQL, and explain whether the calculation applies to the entire result set or to groups. Check carefully for NULL values and distinguish WHERE from HAVING.
