0

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.
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