SQL: Why COUNT(column) Skips NULL Values

The symptom

Two counts of the same table disagree. count(*) gives one number and count(shipped_date) gives a smaller one, so a report that should say "orders" quietly undercounts them. Neither query is broken; they are answering different questions.

Why it happens

count(*) counts rows. count(column) counts only rows where that column is not NULL. If a shipped date is missing for pending or cancelled orders, those rows disappear from the column count.

This behavior is useful when you mean "how many values are filled in," but misleading when you mean "how many rows exist."

The fix

Choose the count that matches the question: count(*) when you mean rows, count(column) when you mean values that are filled in. Both queries below claim to count Garden Shop orders.

select count(shipped_date) as orders
from orders

select count(*) as orders
from orders

Garden Shop has 24 orders, and seven of them — three pending, three processing, one cancelled — have never shipped, so theirshipped_date is NULL andcount(shipped_date) stops at 17. Naming the column you count is what keeps this readable: count(*) as total_orders alongsidecount(shipped_date) as shipped_date_filled, andsum(case when shipped_date is null then 1 else 0 end) when you want the gap itself as a column.

Learn more

• Lesson 4.1 · COUNT
• Lesson 2.6 · Missing values with NULL
• Reference · Aggregates
• Cheatsheet · Aggregates