COUNT() vs COUNT(*)
The fifth aggregate function gets a lesson of its own, because it comes in two forms that look almost identical and answer genuinely different questions. Telling them apart is worth a mark in nearly every paper.
1Two different questions
Counts the values actually present. A NULL is not a value, so it is not counted.
Counts rows. It does not look inside them, so NULLs make no difference.
2The difference, run
The teachers table has twelve rows, and one teacher has no salary recorded:
Eleven and twelve, from the same table, in the same moment. The difference is exactly the one NULL salary.
Put three counts side by side and the pattern is unmistakable. Twelve students, two with no percentage and two with no city:
max, min, sum, avg and count(column) — ignores NULL. count(*) is the single exception, because it is counting rows rather than looking at values. If you remember one sentence from this chapter, make it that one.3Which one does the question want?
| The question asks for… | Use | Because |
|---|---|---|
| The number of teachers in the school | count(*) | Every teacher is a row, whatever is missing from it |
| How many teachers have a salary on record | count(sal) | You are counting the values present in one column |
| How many students have no city recorded | count(*) … where city is null | Filter to the missing ones first, then count the rows |
| How many different cities students come from | count(distinct city) | distinct removes the repeats before counting |
That last one joins the two ideas: distinct from the SELECT chapter, inside count(). It counts each different value once.
4A habit worth having
When a question just says “count the records” or “how many rows”, count(*) is the safe answer — it cannot be caught out by a NULL somewhere in the row. Reach for count(column) only when the column itself is the point of the question.
5Recap
Counts rows. Never skips anything.
Counts non-NULL entries in that column.
Exactly the number of NULLs in the column.
Counts each different value once.
A table has 12 rows. 2 have NULL in city. What does count(city) return?
Which aggregate does NOT ignore NULL values?
You want the total number of students in a table where some columns are incomplete. Which is safest?