Topic 9 · Databases · 9.7

SQL COUNT Explained Simply

COUNT answers one clear question: how many records are included in the result?

Start the lesson Explore the Learning Hub
Invitation

How many records match the question?

A normal SELECT query displays records from a database.

Sometimes the user does not need to see every matching record. They only need to know how many there are.

COUNT returns one number: the number of records included in the query result.
Figure 1
Three matching records
NameHouse
AminaBlue
BenRed
ChenBlue
DaraBlue
Blue records = 3
Big Idea

COUNT replaces a list with a total

The asterisk means that SQL should count the records returned by the query.

SQL patternCOUNT records
SELECT COUNT(*)
FROM TableName;
COUNT(*) counts the number of records in the result.
FutureLogic Bridge

COUNT is like counting names on a register

A teacher could read every name on a class register, but sometimes only the class total is needed.

COUNT works in the same way. It checks the records and returns the total number rather than displaying the complete list.

The records are still there. COUNT changes the result into one useful number.
Figure 2
The register bridge
RegisterA list of student names
↓
Class total24 students
Worked Example

Count the students in the Blue house

The database table is called Students. The query must count only records where the House field contains Blue.

Database Students table
→
Query COUNT records where House = "Blue"
→
Output 3
SQL queryCount matching records
SELECT COUNT(*)
FROM Students
WHERE House = "Blue";
Query Output 3

Three records match the condition.

Read the command word: when the question asks “how many,” COUNT is usually the required SQL function.
COUNT and WHERE

Use WHERE when only some records should be counted

Count every record

All recordsNo condition
SELECT COUNT(*)
FROM Students;

This returns the total number of records in the Students table.

Count matching records

Selected recordsUses WHERE
SELECT COUNT(*)
FROM Students
WHERE House = "Blue";

This returns only the number of students in the Blue house.

Exam Tip

Keep the SQL structure in the correct order

1SELECT COUNT(*)
2FROM the correct table
3Add WHERE when needed
Full-mark habit: finish the SQL statement with a semicolon.
Common Mistake

Do not confuse COUNT with SUM

COUNT tells you how many records there are. SUM adds numerical values stored in a field.

COUNT

“How many sales were made?”

Returns the number of records.

SUM

“What is the total value of all sales?”

Adds the values in a numerical field.
Common error: using SUM when the question asks for the number of records. The correct answer is: “Use COUNT because the query must return how many records match.”
Summary

COUNT turns matching records into one number

1COUNT(*) counts records.
2FROM identifies the table.
3WHERE limits which records are counted.
Remember: Database → Query → Output.