Concept · SQL & Querying

COUNT(*) counts rows, COUNT(col) counts non-NULLs, COUNT(DISTINCT col) counts unique values — three different questions.

COUNT(*) counts rows. COUNT(col) counts only rows where col is not NULL. COUNT(DISTINCT col) counts distinct non-NULL values. Choosing the wrong one silently changes the total.

See it break

ballots

voter	choice
1	A
2	NULL
3	A
4	B

The trap

SELECT
  COUNT(choice) AS n
FROM
  ballots;

The fix

SELECT
  COUNT(*) AS n
FROM
  ballots;

The trap

The fix

The 'ballots cast' total reads 3, not 4 — COUNT(choice) silently skipped the blank ballot; COUNT(*) counts the row regardless.

The rule

Count rows with COUNT(*), count present values with COUNT(col), and count unique values with COUNT(DISTINCT col) — pick the one the question asks for.

Shows up elsewhere

Aggregates ignore NULLs (except COUNT(*))

Try one →Prove it in practice →