Aggregate Functions and the NULL Trap
COUNT, SUM, AVG, MIN and MAX over a live table, and the one thing they all do with NULL that catches people out.
Overview
Many rows in, one value out
An aggregate function takes a set of rows and returns a single value. With no GROUP BY, the set is the whole table; with one, it is each group in turn.
The five that matter are COUNT, SUM, AVG, MIN and MAX. Run the starter query and all five appear side by side over the same nine rows. The numbers do not agree with each other in the way you might expect, and the reason is NULL.
Aggregate Functions and the NULL Trap
Worth knowing
GROUP BY the whole table is one group.COUNT(*) counts rows. COUNT(col) counts rows where col is not NULL. The difference is your missing data.AVG divides by the number of values it actually saw, not by the number of rows.COUNT, which is 0.Aggregate Functions and the NULL Trap
Five functions that collapse many rows into one, and the one behaviour they share that quietly changes your answers.
COUNT(*) and COUNT(col) are different functions
This is the single most useful distinction on the page.
COUNT(*) counts rows. It does not look at any column, cannot be affected by NULL, and answers "how many records are there".
COUNT(temp_c) counts non-NULL values in that column. It answers "how many records actually have a reading".
The table has nine rows and four of them have no temperature. So COUNT(*) is 9 and COUNT(temp_c) is 5, and the gap between them is a free measure of missing data:
SELECT COUNT(*) - COUNT(temp_c) AS missing FROM readings;That one line is worth reaching for whenever a column's quality is in question. It needs no subquery, no IS NULL filter and no second pass.
Everything else skips NULL silently
SUM, AVG, MIN and MAX all ignore NULL. They do not fail, warn, or return NULL because a NULL was present — they behave as though those rows were not there.
For SUM, MIN and MAX this is almost always what you want. For AVG it is the thing that catches people, because AVG divides by the count of values, not the count of rows:
AVG(temp_c) = SUM(temp_c) / COUNT(temp_c)Run the second variant. Three numbers appear that all have a claim to being "the average temperature", and they differ substantially:
| Expression | What it means |
|---|---|
AVG(temp_c) | mean of the readings that exist |
AVG(COALESCE(temp_c, 0)) | mean if a broken sensor means zero degrees |
SUM(temp_c) / COUNT(*) | total spread across all rows including the broken ones |
Which is correct depends entirely on what NULL means in your data. If the sensor failed, the first is right and the others are nonsense — a broken sensor is not a reading of zero. If NULL means "no sales that day", the second may well be right. The database cannot decide this for you, and it will not ask.
The empty group
Run the fourth variant. It restricts to the rows that have no reading at all, so there are four rows and zero values, and every aggregate has to say what it does with nothing.
COUNT(*) is 4 and COUNT(temp_c) is 0, as expected. SUM, AVG, MIN and MAX all come back NULL — not zero, and not an error. The rule is that an aggregate over zero values returns NULL, and the single exception is COUNT, which returns 0 because counting nothing genuinely is zero.
This matters when an aggregate feeds arithmetic. SUM(amount) * 1.2 over a group with no rows is NULL, and NULL propagates through every operation it touches until something wraps it in COALESCE.
GROUP BY and the shape of the result
Adding GROUP BY station changes the result from one row to one row per station. The rule that follows is the one beginners trip on: every column in the SELECT list must either be aggregated or appear in the GROUP BY.
The reason is that the query has to produce exactly one value per group. Given three readings for north, there is no answer to "what is the id" — there are three, and the database will not choose one. Some engines historically allowed it and returned an arbitrary row, which is worse than an error because it works until it does not.
Three averages of the same column
The "AVG divides by the count of values, not rows" rule is best seen as three numbers that all claim to be the average. Five rows, two of them with a NULL reading, the three present values being 10, 20 and 30 (sum 60):
| expression | value | what it computed |
|---|---|---|
AVG(t) | 20 | 60 / 3, the mean of the readings that exist |
SUM(t) / COUNT(*) | 12 | 60 / 5, the total spread across all rows |
AVG(COALESCE(t, 0)) | 12 | same, treating a missing reading as zero |
Confirmed in the SQLite this page runs. The gap between 20 and 12 is not rounding — it is the two NULLs, counted by COUNT(*) and skipped by AVG. Which one is correct is a question about the data, not the SQL: if a NULL means a sensor failed, 20 is right and 12 invents readings of zero; if it means "no sales that day", 12 is right and 20 overstates the typical day. The database will compute whichever you write and warn you about neither.
The empty group returns NULL, not zero
There is one more NULL behaviour that breaks arithmetic downstream: an aggregate over *no values* returns NULL, and only COUNT returns 0. Restrict to rows that have no reading at all — a real group with zero values — and:
| aggregate | over an empty group |
|---|---|
COUNT(*) | 0 |
COUNT(t) | 0 |
SUM, AVG, MIN, MAX | NULL |
SUM of nothing being NULL rather than 0 is the one that bites, because NULL then propagates: SUM(amount) * 1.2 over an empty group is NULL, and it stays NULL through every operation until something wraps it in COALESCE. A report that subtotals by group and then does arithmetic on the subtotals will show blanks, not zeros, for the empty groups — and a blank where a number was expected is how this surfaces, usually in front of someone who trusts the report.
Where it goes wrong
Using AVG on data where NULL means zero. The average silently rises. Use COALESCE when absence really is a zero.
Assuming COUNT(col) is a faster COUNT(*). They answer different questions. On a nullable column they return different numbers.
Filtering aggregates in WHERE. WHERE COUNT(*) > 5 is an error — WHERE runs before grouping, so no counts exist yet. That is what [HAVING](having_in_sql.html) is for.
Forgetting that NULL breaks arithmetic. One NULL anywhere in an expression makes the whole expression NULL.
Check yourself
0 of …Answer without scrolling back up.
What is the difference between COUNT(*) and COUNT(temp_c)?
AVG(temp_c) over nine rows, four of them NULL, divides the sum by what?
What does SUM return over a group containing no non-NULL values?
Aggregate Functions and the NULL Trap
An aggregate function takes a set of rows and returns a single value. With no GROUP BY, the set is the whole table; with one, it is each group in turn.