Count matching records with COUNT
Use COUNT with a suitable field to report how many table records meet a query condition.
Exam scope and assessment notes
This sequence follows Cambridge IGCSE Computer Science 0478, 2026–2028 syllabus Version 6, Topic 9. It covers single-table databases only and the specified SELECT, FROM, WHERE, ORDER BY, SUM, COUNT, AND and OR content. The official specimen also demonstrates NOT in a filter. Multi-table joins, GROUP BY, database implementation and the separate 2029–2031 syllabus are not mapped here. Practice is original, self-checked and does not award an official Cambridge grade. Full timed papers and human subject-teacher review remain outstanding.
Before you begin
- Be able to read a small table and compare values. Each lesson introduces the database and SQL terms it uses.
Work on paper. Try each question before revealing help, and explain why your method works.
Quick readiness check
What field is a safe choice to count every record?
What you will learn
- Use COUNT in a SELECT query
- Count records or filtered records
- Choose a field present for every counted record
COUNT returns a number of values
COUNT(FieldName) returns a count for the specified field. For a single-table question, use a field that is present in every record, such as a primary-key field, when you intend to count records.
COUNT is an aggregate, so the result is a number rather than one output value for each source row.
WHERE can limit the records being counted
Place WHERE before ORDER BY when only matching rows should contribute to the count.
Check the table values carefully. If the chosen field is blank in some records, COUNT on that field may not equal a total row count; use an always-present identifier for an unambiguous count.
Pause and explain
How do you count only performances with Type = Jazz?
Worked example
A table SHOWS has one unique ShowNumber for each record. Write a query to count all shows.
Show the worked solution
- Choose the always-present ShowNumber field.
- Apply COUNT to that field.
- Name SHOWS as the source.
Answer SELECT COUNT(ShowNumber) FROM SHOWS;
Try a topic test
Choose Easy, Normal or Difficult. Save your answers and open worked review after finishing.
Test this topic →From guided practice to a new situation
Write your answer and reasoning first. Use a hint only if you are stuck. The model solution is for self-checking; your written work is not automatically marked.
Write a query to count all rows in BOOKINGS using BookingID, which is present in every row.
Give me a hint
Apply COUNT to the identifier.
Compare my reasoning
- Use COUNT(BookingID).
- Name BOOKINGS after FROM.
- No filter is needed for all rows.
SELECT COUNT(BookingID) FROM BOOKINGS;
Look for these in your work
- Count field
- Source table
There are 12 bookings, 5 with Paid = TRUE. How many should COUNT(BookingID) with WHERE Paid = TRUE return?
Give me a hint
The ID exists in every matching record.
Compare my reasoning
- The WHERE condition keeps five rows.
- Each kept row has one BookingID.
- COUNT returns five.
5.
Look for these in your work
- Filter applied
- Count matches
A student uses COUNT(Comment) to count records, but some comments are blank. Explain why that may undercount and suggest a better field.
Give me a hint
Aggregate on a field guaranteed for each row.
Compare my reasoning
- Blank comment values may not be counted.
- The result can be less than the number of records.
- Use a non-empty primary-key field such as RecordID.
COUNT(Comment) may omit blank values; count RecordID instead if it exists for every record.
Look for these in your work
- Reason
- Safer field
Common mistakes
- Counting a field that may be blank when the goal is number of records
- Expecting COUNT to return every source row
- Placing WHERE after ORDER BY
What can you explain now?
What does COUNT return?
Compare with the explanation
A numerical count
COUNT aggregates matching field values into a number.
After trying it yourself, choose your next review. This is your self-assessment.
Your review choice appears on Today. Sign in to sync it across devices.
Make it stick
Why is a primary-key field useful for counting records?
If you needed the solution, close it and solve the problem again from a blank page. A correct answer is stronger when you can explain the reason for each step.