Concept · SQL & Querying

SUM/AVG/COUNT(col) skip NULLs; COUNT(*) counts rows — the bridge into P04.

The P02→P04 bridge in one row: aggregates ignore NULL inputs, and the divergence surfaces the question you didn't know you were choosing between — average of the finishers (AVG: 15) vs average per entrant (SUM over all rows: 10). A DNF averaged as 'skipped' flatters the maze; averaged as zero it slanders the finishers. Any AVG over a nullable column gets one line stating whose denominator it is.

See it break

runs

t
10
NULL
20

The trap

SELECT
  AVG(t) AS avg_t
FROM
  runs;

The fix

SELECT
  SUM(t) * 1.0 / COUNT(*) AS per_entrant
FROM
  runs;

The trap

The fix

AVG read 15 (average of the finishers) where the dashboard meant 10 (average per entrant) — SUM/AVG/COUNT(col) skip NULLs while COUNT(*) counts rows, so the two averages answer different questions the moment a NULL exists.

The rule

AVG for per-non-null; SUM/COUNT(*) or COALESCE for per-row; the denominator sentence.

Shows up elsewhere

COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col)Average of averages

Try one →Prove it in practice →