0
Challenge · 11SQL I: Asking Questions of Data
Aggregates: COUNT(*) vs COUNT(col)
SQLused in ~85% of apps
Your DevLog homepage needs one number: "3 posts." Then a second: "2 with a reading time." Then a third: "3.0 minutes, on average." Every one of those looks like it comes from the same family of function — an aggregate — and every junior writes `count(*)` for all three and gets the first one right and the other two subtly, silently wrong.
Core idea. An aggregate collapses many rows into one value. COUNT(*) counts ROWS — full stop, NULLs and all. Every other aggregate — COUNT(col), SUM, AVG, MIN, MAX — looks only at the NON-NULL VALUES of the column you gave it, which means AVG divides by however many values it actually saw, not by how many rows existed.
poke it, drag it,
try to break it
try to break it
no slides here.
you drive.
you drive.
Playground, this isn't a screenshot
posts.like_count
10
NULL
5
3 rows, one NULL
count(*)=3
one per ROW — the NULL row is still a row
count(*) is the only aggregate that counts the NULL cell. It counts rows, full stop. This is why a LEFT JOIN's lonely, NULL-padded row still bumps count(*) to 1.
count(*) counts rows. Everything else reads only non-NULL values — and avg divides by the non-NULL count, not the row count.
↑ play a little first, then dig in