SQL aggregate functions — COUNT, SUM, AVG, MIN, and MAX — perform calculations across multiple rows and return a single result. Combined with GROUP BY and HAVING clauses, they allow GCSE students to answer analytical questions such as "how many students passed?" or "what is the average score by class?"
What are SQL aggregate functions and why do they matter?
An aggregate function takes a set of values from multiple rows and reduces them to a single value. Rather than listing every individual row, you answer summary questions about the data.
Imagine a school database with a students table:
| student_id | name | class | mark |
|---|---|---|---|
| 1 | Priya | 10A | 78 |
| 2 | Marcus | 10B | 62 |
| 3 | Aisha | 10A | 91 |
| 4 | Leon | 10B | 55 |
| 5 | Zara | 10A | 84 |
Instead of listing all rows, aggregate functions let you ask: "What is the average mark in 10A?" or "How many students are in each class?"
What does each aggregate function do?
| Function | Returns | Example use |
|---|---|---|
COUNT(column) |
Number of non-NULL values in the column | Count how many students have a recorded mark |
COUNT(*) |
Total number of rows | Count all students |
SUM(column) |
Total of all values | Total marks scored by all students |
AVG(column) |
Mean average of all values | Average mark across the school |
MIN(column) |
Lowest value | The lowest mark achieved |
MAX(column) |
Highest value | The highest mark achieved |
All aggregate functions ignore NULL values (empty fields) except COUNT(*), which counts every row regardless.
How do you write a basic aggregate query?
Syntax:
SELECT aggregate_function(column)
FROM table_name
WHERE condition;
Worked examples using the students table:
-- How many students are there in total?
SELECT COUNT(*) FROM students;
-- Result: 5
-- What is the average mark?
SELECT AVG(mark) FROM students;
-- Result: 74.0
-- What is the highest and lowest mark?
SELECT MAX(mark), MIN(mark) FROM students;
-- Result: 91, 55
-- What is the total of all marks?
SELECT SUM(mark) FROM students;
-- Result: 370
-- How many students scored above 70?
SELECT COUNT(*) FROM students WHERE mark > 70;
-- Result: 3
What does GROUP BY do and how do you use it?
GROUP BY splits rows into groups before applying an aggregate function, producing one result row per group.
Syntax:
SELECT column, aggregate_function(column)
FROM table_name
GROUP BY column;
Worked example — class averages:
SELECT class, AVG(mark) AS average_mark, COUNT(*) AS num_students
FROM students
GROUP BY class;
| class | average_mark | num_students |
|---|---|---|
| 10A | 84.33 | 3 |
| 10B | 58.50 | 2 |
The database first groups all 10A rows together and all 10B rows together, then computes AVG and COUNT independently for each group.
Key rule: every column in the SELECT list that is NOT inside an aggregate function must appear in the GROUP BY clause.
What does HAVING do, and how does it differ from WHERE?
WHERE filters individual rows before grouping; HAVING filters groups after aggregation.
-- WHERE: filter individual rows first
SELECT class, AVG(mark) AS average_mark
FROM students
WHERE mark >= 50 -- rows where mark < 50 are excluded first
GROUP BY class;
-- HAVING: filter groups after aggregation
SELECT class, AVG(mark) AS average_mark
FROM students
GROUP BY class
HAVING AVG(mark) >= 70; -- only show classes with an average of 70 or above
Result of the HAVING query (only 10A qualifies with 84.33):
| class | average_mark |
|---|---|
| 10A | 84.33 |
A useful memory aid: WHERE works on rows; HAVING works on groups.
How are aggregate functions combined in a complete query?
A full query can include all the clauses in this order:
SELECT class, COUNT(*) AS num_students, AVG(mark) AS avg_mark, MAX(mark) AS top_mark
FROM students
WHERE mark IS NOT NULL
GROUP BY class
HAVING COUNT(*) >= 2
ORDER BY avg_mark DESC;
This query:
- Excludes students with no recorded mark (
WHERE mark IS NOT NULL) - Groups remaining rows by class
- Keeps only groups with two or more students (
HAVING COUNT(*) >= 2) - Displays class name, student count, average mark, and highest mark
- Sorts results from highest average to lowest
Frequently asked questions
Do I need to know GROUP BY and HAVING for the GCSE exam?
Yes — both AQA and OCR GCSE Computer Science specifications include SQL querying, and GROUP BY is commonly tested. You should be able to write queries using SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY, as well as all five aggregate functions. Check your specific board's specification for the precise list.
What is the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts every row in the result, including rows where the specified column is NULL. COUNT(column) counts only rows where that column has a non-NULL value. If a student has no recorded mark (NULL), COUNT(*) counts that student but COUNT(mark) does not. In most exam questions the distinction does not matter, but it is worth understanding for accuracy.
Can aggregate functions be used in a WHERE clause?
No — aggregate functions cannot appear in a WHERE clause because WHERE operates on individual rows before grouping occurs, and aggregates by definition work across multiple rows. To filter on an aggregate result, use HAVING after GROUP BY. This is a common mistake in exam answers.
What does AS do in a SELECT statement?
AS creates an alias — a temporary name for the column in the output. SELECT AVG(mark) AS average_mark means the result column is labelled "average_mark" rather than "AVG(mark)". Aliases make output easier to read and are often required when joining or referencing aggregate results in outer queries. They do not change the underlying data.
Struggling with SQL queries for your GCSE? Professor Turing at aitutors.me will build and trace every query with you until it clicks.