COUNT

Hook: COUNT looks tiny, but interviewers love it because one NULL can quietly change the answer.

Question: What does COUNT do in SQL?

Answer: COUNT is an aggregate function that returns how many rows or values match your query. COUNT(*) counts every row, COUNT(column) counts only non-NULL values in that column, and COUNT(DISTINCT column) counts unique non-NULL values.

Interview-Ready Answer: “In SQL, COUNT is the aggregate I use to turn rows into a number. I usually reach for COUNT(*) when I want total rows, COUNT(column) when I want only non-NULL values, and COUNT(DISTINCT column) when I want unique values. One subtle but important detail is that COUNT returns 0 on an empty result set, and COUNT(column) will silently skip NULLs, so I choose the form carefully.”

What COUNT is

Detailed Explanation: COUNT is an aggregate function, meaning it collapses many rows into one number. A simple mental model is a tally counter: each qualifying row clicks the counter forward once.

How it works under the hood

1. The database reads the rows that survive the WHERE clause.
2. For COUNT(*), every surviving row increments the total, even if some columns are NULL.
3. For COUNT(column), only rows where that column is not NULL are counted.
4. For COUNT(DISTINCT column), the engine first tracks which non-NULL values it has already seen, then returns how many unique values remain.
5. If you add GROUP BY, the database repeats that process separately for each group and returns one count per group.

Choosing the right form

Interview detail: COUNT(1) usually returns the same result as COUNT(*), and modern optimizers normally treat them the same. Use COUNT(*) because it is clearer and signals that you mean rows, not a specific column.

When and why to use it

• Totals: “How many orders were placed?”
• Data quality checks: “How many emails are missing?”
• Uniqueness checks: “How many distinct customers logged in?”
• Conditional metrics: “How many orders are paid?”

Performance and edge cases

• Exact counts are usually not free. In many databases, a filtered COUNT still needs to visit every matching row, so think O(n) time for the matching rows.
• COUNT(*) with no filter is often the easiest form for the engine to optimize, but you should still assume a scan unless you know your storage engine can answer from metadata.
• COUNT(DISTINCT col) is heavier because the engine must remember what it has seen. That is often O(k) memory for k unique values, and large distinct counts may spill to disk.
• With GROUP BY, memory grows with the number of groups. A report with 100,000 groups needs 100,000 counters.
• If no rows match, COUNT returns 0, not NULL. That makes it safer than many other aggregates for empty sets.

Memory rule: COUNT is a turnstile, not a microscope. It counts what passes through, and the exact gate you choose decides whether NULLs, duplicates, or unique values get in.

Real-World Story: In a checkout service, a dashboard shows how many paid orders came in every minute. A developer changes the query from COUNT(*) to COUNT(status) because status feels like the obvious field, but some legacy imported rows have NULL status. The chart suddenly drops by a few percent, finance opens an incident, and the logs are clean because the SQL is valid; the bug is semantic, not syntactic. The fix is to count rows directly and use an explicit filter for the business rule, such as WHERE status = 'paid'.

What goes wrong: the symptom is an underreported metric, not an error. Users may see fewer orders, alerts may fire, and teams waste time searching for missing data that is actually present but hidden by NULL-handling rules.

Follow-up & Tricky Questions:

• How is COUNT(*) different from COUNT(1)? They normally return the same result. Most optimizers treat them the same, so COUNT(*) is preferred because it reads like “count rows.”
• What happens when you use COUNT with GROUP BY? You get one count per group, not one count for the whole table. Each group is counted independently after the rows are split.
• How do you count only rows that meet a condition? The simplest way is COUNT(*) with a WHERE clause. If you need a conditional count inside a bigger select, COUNT(CASE WHEN ... THEN 1 END) works because the false branch becomes NULL and is ignored.
• Does COUNT(DISTINCT col) include NULL? No, NULL values are ignored before distinct counting happens. That is a common source of off-by-one mistakes in reports.
• Can COUNT be expensive on large tables? Yes. Exact counts often require reading all matching rows, and COUNT(DISTINCT) may need hash memory or a sort, so big reports can spill to disk.
• Does COUNT(*) on an empty table return NULL? No, it returns 0. That is one reason it is so useful in dashboards and checks.
• Can I do COUNT(DISTINCT a, b) everywhere? No. Standard SQL defines a single expression here, and multi-column distinct counts are dialect-specific extensions. Always check your database’s syntax.
• Does COUNT(column) count NULLs? No. It only counts non-NULL values, which is why it can silently undercount when the column is incomplete.

Tricky gotcha: Many candidates say “COUNT counts rows,” but that is only fully true for COUNT(*). The exact form you choose changes how NULLs and duplicates are treated.

Common Mistakes:

• Using COUNT(col) when you mean total rows. Correction: use COUNT(*) if you want every row, because COUNT(col) skips NULLs.
• Expecting COUNT(DISTINCT col) to count NULL. Correction: NULL is ignored, so the result is only unique non-NULL values.
• Thinking COUNT returns NULL on no matches. Correction: it returns 0, which is one of its most convenient behaviors.
• Using WHERE when the filter belongs after aggregation. Correction: use HAVING for conditions on aggregated results such as COUNT(*) > 10.

Memory Hook: Think of a subway turnstile: COUNT(*) lets every rider through, COUNT(col) only lets riders with a non-NULL ticket through, and COUNT(DISTINCT col) checks the ticket number so the same rider is not counted twice.

Cheat Sheet:

• COUNT(*) = total rows after filters.
• COUNT(col) = non-NULL values in that column.
• COUNT(DISTINCT col) = unique non-NULL values.
• No matching rows still means 0.
• COUNT(1) is usually the same as COUNT(*), but COUNT(*) is clearer.
• Large DISTINCT counts may be slower because the engine must remember seen values.

Practice Tasks:

• Write a query that counts all rows in a table of users.
• Write a query that counts only users with a non-NULL email address.
• Write a query that counts distinct countries per signup month using GROUP BY.