SQL Aggregates and NULL
Every aggregate function silently ignores NULLs — except COUNT(*), which is the one exception.
What & Why
SUM, AVG, MIN, and MAX all skip NULL values as if those rows didn't exist for that column. COUNT(*) is the one exception — it counts every row regardless of NULLs anywhere in it.
See How It Works
BUSINESS QUESTION
Watch how AVG(nps_score) handles product feedback rows where the score is NULL.
| id | user_id | feature_id | rating | nps_score | comment | created_at |
|---|---|---|---|---|---|---|
| 1 | 101 | 1 | 5 | 9 | Clear and useful | 2024-01-25 12:15:00+00 |
| 2 | 102 | 2 | 4 | NULL | Export worked well | 2024-02-20 15:40:00+00 |
| 3 | 103 | 1 | 4 | 7 | Helpful setup flow | 2024-03-18 09:05:00+00 |
| 4 | 104 | 3 | 3 | NULL | Needs clearer timing | 2024-04-08 17:30:00+00 |
Watch the aggregate accumulateRow 1 of 4
AVG(nps_score)
| row | feedback | column | nps_score | status | running avg |
|---|---|---|---|---|---|
| 1 | Feedback #1 | nps_score | 9 | ADD | 9 |
| 2 | Feedback #2 | nps_score | NULL | WAIT | — |
| 3 | Feedback #3 | nps_score | 7 | WAIT | — |
| 4 | Feedback #4 | nps_score | NULL | WAIT | — |
Running result
AVG(nps_score) = 99 is added; the running average becomes 9.
EXAMPLE QUERY
SELECT AVG(nps_score) AS avg_nps_score
FROM product.feedback;Now You Try
Practice this concept
Marketing wants to compare all leads with leads that have qualification and conversion timestamps.
Available schema
marketingPrefix tables with marketing.table_name.
total_leadsqualified_leadsconverted_leadsmarketing.leads| Column | Type |
|---|---|
| id | integer |
| campaign_id | integer |
| text | |
| created_at | timestamp with time zone |
| qualified_at | timestamp with time zone |
| converted_at | timestamp with time zone |
| lead_score | integer |
| source | text |
| country | text |
| archive_status | text |
query.sql
Sign up free to try it on a real business scenario