What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
SHORT ANSWER
COUNT(*) counts all rows, COUNT(column) counts rows where that column is not NULL, and COUNT(DISTINCT column) counts the different non-NULL values.
Explain it simply
Imagine an attendance sheet with a column for mobile number. COUNT(*) counts every student on the sheet. COUNT(mobile) counts only students who wrote a number. COUNT(DISTINCT mobile) counts the different numbers, so two siblings sharing one phone count once.
Example
Table students:
| id | name | mobile |
|---|---|---|
| 1 | Asha | 98100 |
| 2 | Ravi | NULL |
| 3 | Neha | 98100 |
| 4 | Imran | 97000 |
sql
SELECT COUNT(*) AS all_rows, -- 4
COUNT(mobile) AS with_mobile, -- 3
COUNT(DISTINCT mobile) AS unique_mobile -- 2
FROM students;- 4 rows exist.
- Ravi's NULL is skipped, so 3.
- 98100 appears twice and counts once, so 2 distinct values.
COUNT(1) behaves the same as COUNT(*); modern optimizers treat them identically.
What to say in the interview
"COUNT(*) counts rows regardless of NULLs. COUNT(col) ignores NULLs in that column. COUNT(DISTINCT col) also removes duplicates. On an empty table COUNT returns 0, while SUM, AVG and MAX return NULL."
Common mistakes
- Using
COUNT(column)to count rows and silently losing the NULL ones.