Lesson 8 of 13 · 18 min
Aggregates and NULL
By the end of this lesson you can predict the result of every aggregate in the presence of NULLs, use COALESCE around an aggregate to report 0 instead of NULL, and decide whether a missing value should be ignored or counted as zero.
Every aggregate except COUNT(*) ignores NULL inputs. It is as if the rows with a NULL in that column were not there for that one aggregate:
| aggregate | what happens to NULLs | result when every input is NULL (or there are no rows) |
|---|---|---|
COUNT(*) | counted (it counts rows) | 0 |
COUNT(x) | skipped | 0 |
SUM(x) | skipped | NULL |
AVG(x) | skipped, both in the total and in the divisor | NULL |
MIN(x) / MAX(x) | skipped | NULL |
Two consequences surprise people: AVG divides by the number of non-NULL values, not by the number of rows; and SUM over nothing is NULL, not 0.
This lesson, its 5 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons