The big idea: An aggregate function collapses many rows into a single value.
COUNT how many · SUM the total · AVG the mean · MIN and MAX the extremes. Everything else in this topic is about controlling which rows they see.
SELECT COUNT(*) AS orders,
SUM(total) AS revenue,
AVG(total) AS average_order,
MAX(total) AS biggest
FROM Orders
WHERE order_date >= '2025-01-01';COUNT(*) and COUNT(column) differ: COUNT(*) counts rows. COUNT(column) counts rows where that column is not null.
After a LEFT JOIN this is the difference between a customer with no orders scoring 1 and scoring 0 — and it is the single most examined detail here.
AVERAGE in the guide, AVG in SQL: The IB guide lists the five aggregates as AVERAGE, COUNT, MAX, MIN, SUM. In SQL the average is written AVG(column). Grouped totals like these are what reports and managers' decisions are built on.
Free preview
This is the free notes preview
You're reading the free notes. Aimnova Pro unlocks the full study experience — and you can try it with your first topic free to keep:
- FlashcardsLock in vocabulary and key terms with spaced repetition.
- Practice questionsAnswer exam-style questions and get instant AI marking.
- Mock exams & past-paper vaultSit full mocks and see exactly how examiners award marks.
- Personalised study planA daily plan built around your exam date and weak areas.
| Clause | Filters | Applied |
|---|---|---|
| WHERE | Individual rows | Before grouping |
| GROUP BY | — | Forms the groups |
| HAVING | Whole groups | After grouping |
SELECT region,
COUNT(*) AS orders,
SUM(total) AS revenue
FROM Orders
WHERE order_date >= '2025-01-01' -- rows, before grouping
GROUP BY region
HAVING SUM(total) > 50000 -- groups, after grouping
ORDER BY revenue DESC;Why WHERE cannot test an aggregate: WHERE is evaluated row by row, before any group exists. At that moment there is no total to compare against — so WHERE SUM(total) > 50000 is not merely wrong, it is meaningless.
A condition on an aggregate needs HAVING, always.
Every non-aggregated column must be grouped: If a column appears in SELECT and is not inside an aggregate, it must appear in GROUP BY.
Otherwise the database is being asked which of several values to show for a group, and there is no answer.
Study smarter, not longer
Most students waste 40% of study time on topics they already know. Our AI tracks your progress and optimizes every minute.
The behaviour to know
- AVG, SUM, MIN, MAX ignore nulls — they are skipped, not treated as zero
- So AVG over 10 rows with 3 nulls divides by 7, not 10
- That is usually what you want — but only if you know it is happening
- COUNT(*) is the exception: it counts every row regardless
- A null is not zero and not an empty string — it is the absence of a value
State the divisor: If a question gives a column with missing values, say explicitly what AVG divides by. "The average is over the 7 rows that have a value, not all 10" is frequently the mark.
How this is tested — you must place a condition in WHERE or HAVING correctly and choose COUNT's argument deliberately. It comes up two ways:
Paper 1 Section A
- Construct a query using an aggregate, 3-5 marks
- State the difference between WHERE and HAVING
- Give the result of an aggregate over given data
Paper 1 Section B — case study
- Write a grouped query with a condition on the total
- Explain why a query returns the wrong count
The classic trap: Putting a condition on a total into WHERE. WHERE runs before any group exists, so there is no total yet — it must be HAVING.
Tables: Customer(customerID, name, city) and Order(orderID, customerID, total). Construct SQL listing each city with its number of customers and total revenue, including cities whose customers have never ordered, for cities with more than 10 customers.
Model answer plan
See the answer broken into clear points, with guidance on how each point earns marks.