COUNT

The Three Faces of COUNT

COUNT is the most-used aggregate function, but it has three distinct forms that behave differently. Using the running orders table:

sql
id | customer_name | amount | order_date | status
 1 | Alice         | 250.00 | 2024-01-05 | completed
 2 | Bob           | NULL   | 2024-01-07 | pending
 3 | Alice         | 400.00 | 2024-02-10 | completed
 4 | Charlie       | 150.00 | 2024-02-15 | cancelled
 5 | Bob           | 300.00 | 2024-03-01 | completed

1. COUNT(*) — counts all rows

sql
SELECT COUNT(*) FROM orders;
-- 5

COUNT(*) counts every row in the result set, regardless of NULLs in any column. It doesn't look at a specific column's value at all — it just counts rows.

2. COUNT(column) — counts non-NULL values in that column

sql
SELECT COUNT(amount) FROM orders;
-- 4

Bob's order has amount = NULL, so it is excluded. COUNT(amount) answers "how many rows have a value for this column?"

sql
SELECT COUNT(status) FROM orders;
-- 5   (status is never NULL here)

3. COUNT(DISTINCT column) — counts distinct non-NULL values

sql
SELECT COUNT(DISTINCT customer_name) FROM orders;
-- 3   (Alice, Bob, Charlie — Alice and Bob each appear twice but count once)

SELECT COUNT(DISTINCT status) FROM orders;
-- 3   (completed, pending, cancelled)

COUNT(DISTINCT ...) first removes duplicate values, then removes NULLs, then counts what's left.

Why This Distinction Matters

These three answer genuinely different business questions:

Mixing them up is a classic reporting bug — e.g. using COUNT(email) when you meant COUNT(*) will silently under-report if any emails are NULL.

COUNT with WHERE

COUNT combines naturally with WHERE to count a filtered subset:

sql
SELECT COUNT(*) FROM orders WHERE status = 'completed';
-- 3

COUNT(1) vs COUNT(*)

You may see COUNT(1) in older code — it behaves identically to COUNT(*) in every modern engine (both count rows, not a column named 1). There's no meaningful performance difference in modern query planners; COUNT(*) is the clearer, more idiomatic choice.

Edge Cases

• COUNT(*) on an empty table returns 0, never NULL (unlike SUM/AVG/MIN/MAX, which return NULL on an empty set).
• COUNT(DISTINCT col1, col2) (counting distinct combinations of two columns) is supported in some dialects (e.g. MySQL, PostgreSQL) but not universally standard.
• COUNT(NULL) — literally counting the constant NULL — always returns 0.

Key Takeaways / Interview Q&A

*Q: What's the difference between COUNT() and COUNT(column)?* A: `COUNT() counts all rows regardless of NULLs; COUNT(column)` counts only the rows where that column is non-NULL.

Q: What does COUNT(DISTINCT column) do? A: Counts the number of unique, non-NULL values in that column.

Q: Does COUNT() ever return NULL? A: No. Unlike SUM/AVG/MIN/MAX, COUNT always returns a number — 0 if there's nothing to count, never NULL.

*Q: Is COUNT(1) faster than COUNT()?* A: No, they're equivalent in every modern SQL engine; it's purely a style preference (and COUNT() is generally preferred as the standard idiom).