Extract insight from data. SUM adds a numeric field. COUNT counts records. Two functions, one rule: how much? or how many? Get that right and 90% of Cambridge aggregation questions fall into place.
In Labs A and B you learned how a database is shaped and how to ask it questions. In Lab C you learn how to squeeze insight out of the answers. Instead of listing every record, you'll return a single number — a total, or a count.
Cambridge tests two aggregation functions and only two: SUM (adds up a numeric field) and COUNT (counts records). Every past-paper aggregation question is one of these — sometimes both, side by side. The core skill is knowing which one the question is asking for.
Before you start: you need everything from Labs A and B — the six data types, primary keys, and the four query clauses (SELECT, FROM, WHERE, ORDER BY). If any of that feels shaky, jump back for five minutes.
SUM adds together the values of a numeric field across all matching records and returns a single number.SELECT SUM(FieldName) FROM Table;SUM with a WHERE clause to total only the records that match a condition.SUM works on Integer and Real fields only — never on Text, Boolean, or Character.COUNT counts the number of records and returns a single whole number.SELECT COUNT(*) FROM Table; or SELECT COUNT(FieldName) FROM Table;COUNT with a WHERE clause to count only records that match a condition.SUM or COUNT statement in plain English — a common Cambridge question style (2023 Songs, 2024 Contract).| Term | Meaning |
|---|---|
| Aggregation | Any SQL operation that turns many records into a single value. SUM and COUNT are the two Cambridge tests. |
SUM(FieldName) | An SQL function that adds the values of a numeric field across all matching records. Returns one number. |
COUNT(*) | An SQL function that returns the total number of records — including any with null values. |
COUNT(FieldName) | An SQL function that returns the number of records where that field has a non-null value. |
| Numeric field | A field whose data type is Integer or Real. Only numeric fields can be summed. |
| Aggregate result | The single number returned by a SUM or COUNT query — never a table of records. |
| "How much?" | The plain-English cue for SUM. Total cost, total population, total minutes. |
| "How many?" | The plain-English cue for COUNT. Number of books, number of drinks, number of capital cities. |
| Filter-then-aggregate | The two-step pattern: WHERE selects the records, then SUM or COUNT reduces them to one number. |
SQL keywords SUM and COUNT are conventionally written UPPERCASE. Cambridge mark schemes accept lowercase but UPPERCASE is the exam-safe habit.
Before you write a single character of SQL, read the question and ask yourself:
How much → SUM. Adding up quantities. Total cost. Total population. Total minutes.
How many → COUNT. Counting things. Number of books. Number of drinks. Number of capital cities.
Get this right and the rest of the query is mechanical. Get it wrong and every mark on the question is lost.
Syntax:
SELECT SUM(FieldName) FROM Table WHERE Condition; (optional)
SUM a Text field, a Boolean field, or a Character field. Doing so is a syntax/runtime error and always a mark loss on write-the-SQL questions.Example prompt: "The Contract table has a Months field. Write SQL to return the total number of months across all contracts." → SELECT SUM(Months) FROM Contract;
Syntax has two forms:
SELECT COUNT(*) FROM Table WHERE Condition; SELECT COUNT(FieldName) FROM Table WHERE Condition;
| Form | What it counts |
|---|---|
COUNT(*) | Every record that matches the WHERE (or every record in the table if no WHERE). Ignores null values because it looks at rows, not fields. |
COUNT(FieldName) | Every matching record where that specific field has a non-null value. If the field is null in some records, those records are skipped. |
Syllabus tip: for most 0478 questions both forms give the same answer (Cambridge sample tables rarely include nulls). But mark schemes accept both — write whichever the question hints at.
The WHERE clause runs first: it selects the records to look at. Then SUM or COUNT reduces those records to a single number.
SELECT SUM(Price) FROM SoftDrinks WHERE Available = TRUE;
"Total price of available drinks." WHERE filters; SUM totals.
SELECT COUNT(*) FROM MajorCity WHERE Capital = TRUE;
"Number of capital cities." WHERE filters; COUNT tallies.
Quoting carries over from Lab B. Text values in WHERE quoted (= "Asia"). Booleans unquoted (= TRUE). Numbers unquoted (> 5). Every WHERE-clause rule you learned in Lab B still applies here.
SUM(Price, Weight) is invalid — one field at a time.ORDER BY. There's nothing to sort — you get one number.Cambridge frequently gives you an SQL statement and asks: "Explain the purpose of this statement." The answer is always three parts:
Cited: 2023 P2 Q9(c) Songs and 2024 P2 (Contract) — both ask students to explain a SUM and a COUNT side by side.
All examples use the SoftDrinks table from Cambridge 2024 s24 P2 Q9 — the past-paper favourite for aggregation.
DrinkID DrinkName Supplier Container SizeCl NumberInStock ReorderLevel Reordered D01 Cola Cambridge Beverages Can 33 30 15 Yes D02 Orange Fizz Cambridge Beverages Bottle 50 12 10 No D03 Lemon Splash Fenland Drinks Can 33 45 20 No D04 Ginger Zing Cambridge Beverages Can 33 8 15 Yes D05 Apple Crush Fenland Drinks Bottle 50 22 10 No D06 Berry Blast Fenland Drinks Can 50 18 15 No D07 Mint Cooler Cambridge Beverages Bottle 33 15 10 Yes
SELECT SUM(NumberInStock) FROM SoftDrinks;
Returns: 150 (30+12+45+8+22+18+15).
Teaching point: no WHERE = every record is included in the sum.
SELECT SUM(NumberInStock) FROM SoftDrinks WHERE Container = "Can";
Returns: 101 (30+45+8+18 — the four drinks in cans).
Teaching point: "Can" is Text — quotes required. Missing them lost marks in the 2024 s24 examiner report. Every quoting rule from Lab B still applies.
SELECT COUNT(*) FROM SoftDrinks;
Returns: 7 (seven drinks in the table).
Teaching point: COUNT(*) counts every record. It doesn't care what's inside — it just counts rows.
SELECT COUNT(*) FROM SoftDrinks WHERE Supplier = "Cambridge Beverages";
Returns: 4 (Cola, Orange Fizz, Ginger Zing, Mint Cooler).
Teaching point: "How many" = COUNT. Notice we're counting records, not adding values.
SELECT COUNT(*) FROM SoftDrinks WHERE Reordered = "Yes";
Returns: 3 (Cola, Ginger Zing, Mint Cooler).
Teaching point: the Reordered field here is Text ("Yes"/"No") not Boolean, so it gets quotes. If it had been a Boolean field (Reordered = TRUE), no quotes. Always check the data type.
SELECT SUM(NumberInStock) FROM SoftDrinks WHERE Container = "Can"; -- Returns 101 (total stock across all Can drinks) SELECT COUNT(*) FROM SoftDrinks WHERE Container = "Can"; -- Returns 4 (number of records that are Cans)
Teaching point: same WHERE, completely different questions. SUM asks "how much stock in total?"; COUNT asks "how many kinds of Can drink?".
SELECT SUM(Minutes) FROM Songs WHERE Genre = "rock";
Model answer: This statement totals the length in minutes of every song from the Songs table where the Genre is "rock". It returns a single number — the total duration of all rock songs.
Teaching point: three parts — what it does · which field · which records. Miss any and you lose a mark.
SELECT COUNT(Title) FROM Songs WHERE Genre = "rock";
Model answer: This statement counts the number of songs in the Songs table where the Genre is "rock". It returns a single whole number — the number of rock song titles recorded.
Teaching point: COUNT(Title) counts records with a non-null Title. On this table it gives the same answer as COUNT(*).
❌ Question: "How much stock in total do we hold of drinks in cans?"
❌ Wrong answer: SELECT COUNT(NumberInStock) FROM SoftDrinks WHERE Container = "Can";
Why wrong: COUNT would return 4 (the number of Can drinks), not the total stock across them (101). The question said "how much" — that's SUM.
❌ Question: "How many drinks are supplied by Cambridge Beverages?"
❌ Wrong answer: SELECT SUM(Supplier) FROM SoftDrinks WHERE Supplier = "Cambridge Beverages";
Why wrong: you can't SUM a Text field — Supplier is a name, not a number. The question said "how many" — that's COUNT.
❌ Writing SELECT SUM(Container) FROM SoftDrinks;
SUM works only on Integer and Real fields. Container is Text, Reordered is Text (or Boolean), Supplier is Text — none of them can be summed. The database will error, and Cambridge will mark the query wrong even if you got the WHERE right.
Cited: 2024 s24 P2 Q9 mark scheme — SUM must be applied to NumberInStock, not any other field.❌ Question: "Total stock of drinks that are in cans"
❌ Wrong answer: SELECT SUM(NumberInStock) FROM SoftDrinks;
Why wrong: this returns 150, the total across every drink. The question specifically filtered to cans — the WHERE clause is required. Read every word of the question.
❌ Writing WHERE Container = Can instead of WHERE Container = "Can"
The quoting rule from Lab B still applies to aggregation queries. Text values in WHERE need quotation marks. This is the most-cited SQL mark loss in the 2024 s24 examiner report — even students who got SUM right often lost the mark for missing the quotes around Can.
Cited: 2024 s24 P2 Q9(b) examiner report — "A significant number of candidates did not put quotation marks around 'Can' in the WHERE statement."❌ Writing WHERE Available = "TRUE" or WHERE Price > "5"
Booleans and numbers are never quoted, even inside SUM/COUNT queries. Text quoted, Boolean unquoted, Numeric unquoted — the rule doesn't change just because you're aggregating.
Cited: 2025 w25 examiner report — Boolean unquoted rule reinforced.❌ Writing SELECT SUM(Price, Volume) FROM SoftDrinks;
Both SUM and COUNT take exactly one argument. If the question needs two totals, that's two separate SELECT statements. Never comma-separate fields inside SUM(...) or COUNT(...).
SUM(FieldName), single field only.
❌ Writing select sum(months) from Contract;
Cambridge mark schemes are case-insensitive on SQL keywords, but every model answer shows them in UPPERCASE. Uppercase is the exam-safe habit — it visually separates SQL keywords from field names and stops you accidentally writing sum as a field name.
❌ Assuming SELECT SUM(Price) FROM SoftDrinks; shows you the individual prices as well as the total.
SUM and COUNT reduce many records to one number. If a Cambridge question asks "how much" or "how many", the answer is one number. If it asks you to also list the records, that's a separate SELECT statement — often shown as part (i) and part (ii) of the same question.
Cited: 2023 P2 Q9(c) Songs — explain-the-purpose questions test that students understand the output is a single value.❌ Writing SELECT SUM(Months) FROM Contract ORDER BY Months;
ORDER BY sorts rows. SUM and COUNT return a single row (one number). There's nothing to sort. Adding ORDER BY to an aggregation query is a syntax error and always a mark loss.
Cited: syllabus 9.5/9.6 combined — ORDER BY is for row-returning queries only, not aggregation.Mark-scheme-phrased chips. The exam-safe habits for aggregation.
COUNT(*) counts every matching record; COUNT(FieldName) counts non-null values in that field.SUM(Price, Weight) is invalid. If you need two totals, write two SELECT statements.;.Click each question to reveal the answer. 0 / 8 revealed
SUM be applied to?COUNT(*) and COUNT(FieldName)?COUNT(*) counts every matching record (looks at rows). COUNT(FieldName) counts records where that field has a non-null value. On most Cambridge tables they give the same answer.SUM or COUNT take more than one field?SUM(Price, Weight) is invalid. Two totals = two separate SELECT statements.ORDER BY makes no sense with SUM or COUNT.SELECT SUM(Months) FROM Contract;", what three parts should your answer include?NumberInStock for every drink sold in a Can from the SoftDrinks table.SELECT SUM(NumberInStock) FROM SoftDrinks WHERE Container = "Can";Key checks: SUM (not COUNT — it's asking for total stock), NumberInStock (the numeric field),
"Can" quoted (Text), ; at the end. This is the exact 2024 s24 shape.You've distilled the last skill in the Databases toolkit. From building a table (Lab A) to querying it (Lab B) to extracting totals and counts (this lab), you now have every SQL move the 0478 syllabus tests.
Next up: Topic 10 Boolean Logic — where you'll learn the logic gates that make databases (and everything else in computing) actually work.
Book Notes is the reference. Learn is the walk-through. If SUM and COUNT are new, start here and go slow. If Book Notes already clicked, skim.
Every query you wrote in Lab B returned rows. Some rows, all rows, sorted rows — but always rows. Aggregation is different. It reads all the matching records and returns one single number.
Lab B query: SELECT City FROM MajorCity WHERE Continent = "Asia";
→ returns 3 rows (Abu Dhabi, Beijing, Jakarta).
Lab C aggregation: SELECT COUNT(*) FROM MajorCity WHERE Continent = "Asia";
→ returns 1 number (3).
Aggregation is a funnel. WHERE selects records; SUM or COUNT squeezes them down to one value. Same table, same WHERE — different aggregation, different result.
Before you write any SQL, look at the plain-English question and ask yourself which of these two phrases fits:
"How much" = SUM
Total. Altogether. Combined. Overall stock. Total minutes. → SUM(FieldName)
"How many" = COUNT
Number of. Count of. How many records. → COUNT(*) or COUNT(FieldName)
Sanity test. "How many cans of Cola are in stock?" — that's ambiguous! It could mean:
Cambridge questions are usually clearer, but always re-read carefully. The wrong pick loses every mark on the question — even if your syntax is perfect.
You can add up prices. You can add up populations. You can add up minutes. You cannot add up names, yes/no flags, or genres.
Sum-able: Integer fields, Real fields. Anything with numeric values.SUM(Price), SUM(Population), SUM(NumberInStock).
Not sum-able: Text, Character, Boolean, Date/Time.SUM(CityName), SUM(Available), SUM(Genre) — all invalid.
Before writing SUM, look at the data type of the field. If it's not Integer or Real, you're using the wrong function. If the question is "how much" but the field isn't numeric — either you need to count records instead, or you need to pick a different field.
When you combine aggregation with a WHERE clause, the order of operations is important:
Every WHERE rule from Lab B still applies. Text values quoted (= "Can"). Booleans unquoted (= TRUE). Numbers unquoted (> 5.00). Logical operators =, <>, <, <=, >, >=. Boolean operators AND, OR.
The 2024 s24 examiner report cited students who correctly used SUM(NumberInStock) but then forgot the quotes around "Can" in the WHERE. They lost the mark on quoting even though the aggregation was right. Lab B's rules never stop mattering.
Time to practise the "how much? how many?" call at SUM or COUNT? — the signature interactive for this lab. Real past-paper scenarios, live sample tables, instant Cambridge-marker feedback.
Read the plain-English business question. Pick SUM or COUNT. Complete the SQL. The bench runs your query against a live sample table and marks it against the Cambridge mark-scheme rules. Every scenario is drawn from a real 0478 Paper 2.
You haven't picked yet.
Pick your function and run your query to see the aggregate result.
Feedback lands here after you run — checks on function choice, field, quoting, WHERE structure, and the value returned.
* or any field.Ten rapid-fire questions on foundations vocabulary. Sixty seconds on the clock. Beat your best score.
Multiple-choice checks. Start on Easy if you're new to the topic; move up as you go. Every attempt is logged in Progress. Adaptive: weak skills come up more often.
Real past-paper style. Type your answer, then self-mark against the mark scheme. Skills feed into Mastery.
The mark-losing traps Cambridge examiners cite year after year on Topic 9.6–9.7 — plus memory triggers to lock the exam-winning phrases in your head.
Every trap here is drawn from an actual Cambridge examiner report on SUM/COUNT. Read them before every mock exam.
When the question says "total", "altogether", or "how much" — it wants SUM, not COUNT. Students who write COUNT(NumberInStock) when the question asks for total stock lose every mark on the query.
"How many" and "number of" always point to COUNT. Students who write SUM(Supplier) to count records also hit the non-numeric error — double mark loss on one query.
SUM works on Integer and Real only. Writing SUM(City), SUM(Genre), or SUM(Available) is a runtime error. Always check the data type of the field before you write SUM.
The top-cited SQL mark loss in aggregation questions. Text values in WHERE clauses must be in quotation marks. WHERE Container = "Can" — quotes are required.
Booleans are never quoted in SQL, even inside aggregation queries. WHERE Available = "TRUE" loses the mark. Correct: WHERE Available = TRUE. The quoting rule from Lab B doesn't change when you add SUM or COUNT.
"Total NumberInStock of drinks that are cans" — the "that are cans" is a filter. Missing the WHERE returns the total across every drink and loses the mark. Every filtering word in the question must map to a WHERE condition.
Cited: 2024 s24 P2 Q9(b) mark scheme — WHERE Container = 'Can' required.Writing SUM(Price, Weight) or COUNT(Title, Artist) is invalid. Both SUM and COUNT take exactly one argument. Two totals = two separate SELECT statements.
ORDER BY sorts rows. Aggregation returns one row (one number). Nothing to sort. Writing SELECT SUM(Months) FROM Contract ORDER BY Months; is a syntax error.
When Cambridge asks you to explain a SUM or COUNT statement (like the 2023 Songs and 2024 Contract questions), the mark scheme wants three parts: what it does · which field · which records. Missing any part loses a mark.
Cited: 2023 P2 Q9(c) Songs and 2024 P2 Q(c) Contract — both explicitly test three-part explanations.The result of aggregation is always a single number — never a table of records. If the exam question expects a list AND a total, that's two queries, not one.
Cited: recurring across 2022–2025 — students misread aggregation results as lists.Short mnemonics for the exam-critical aggregation habits.
How much → SUM. Total. Altogether. Combined.
How many → COUNT. Number of. Count of.
Ask this before you write a single character of SQL. Get it wrong and every mark on the question is lost.
SUM works on Numbers Only. Integer and Real. Never Text, Character, Boolean, or Date/Time. Before writing SUM, glance at the data type of the field. If it's not numeric, either you need COUNT or you need a different field.
COUNT(*) = every matching record.COUNT(FieldName) = records where that field is non-null.
On Cambridge tables the two usually give the same answer, but the syllabus tests the distinction. Write COUNT(*) when in doubt.
One argument in the parentheses. One number out as the result. Never two fields in SUM(). Never a table of records. Aggregation is a funnel — many records in, one number out.
The WHERE quoting rule from Lab B is unchanged. Text values quoted (= "Can"). Booleans unquoted (= TRUE). Numbers unquoted (> 5). The 2024 s24 examiner report cited students who nailed SUM but lost the mark on missing "Can" quotes.
The three-part "explain the purpose" answer:
What it does (totals a field / counts records) → Which field → Which records. Miss any part, lose a mark. Cambridge tested this exact shape in 2023 Songs and 2024 Contract.
No mistakes logged yet. Come back after Practice and Exam.
Nothing in the Vault yet. Save key facts from Book Notes or from question feedback.
Every skill you attempt (in SUM or COUNT?, Practice, or Exam) is scored. Get 3+ attempts with 70%+ accuracy on a skill and it goes green — mastered.
Tap a skill badge to override its status (e.g. mark it Mastered by hand once you're confident, or set it back to In Progress to force more practice).
No attempts yet.
Tick each item as you cover it. Save your progress by leaving this browser open.
Complete Practice and Exam to unlock achievements.
No bookmarks yet. Star a question in Practice or Exam to save it here.
Spot a bug, a confusing explanation, or something to improve? Log it here — feedback is saved locally and reviewed each build cycle.