NULLs in Aggregations
How NULL silently changes COUNT, SUM, AVG, and GROUP BY
Intermediate 10 minNULLaggregationCOALESCE
NULL and Aggregate Functions
Most aggregate functions ignore NULLs — consistently, but sometimes with surprising consequences for your results. Here's how each one behaves:
COUNT(*) is the exception — It counts ALL rows including those with NULLs. It's the only aggregate function that acknowledges NULL rows exist. Use it when you need the true total row count regardless of data completeness.
AVG is the most dangerous — AVG of [100, 200, NULL] returns 150, not 100. It excludes NULLs from both the sum and the count. If you expect NULLs to be zeros, your average will be silently inflated without any error or warning.
Fix with COALESCE — Wrap your column before aggregating:
AVG(COALESCE(column, 0)) converts NULLs to 0 before the calculation begins, giving you results that treat missing values as zeros instead of skipping them.GROUP BY creates a NULL group — All rows with NULL in the grouped column land together in a single "NULL group." This can appear as an unexpected blank row in your reports if you're not watching for it.
Knowledge Canvas
NULL + Aggregates
How NULLs interact with aggregate functions
- All aggregates (except COUNT(*)) skip NULL values
- COUNT(*) counts rows; COUNT(col) counts non-NULLs
- SUM/AVG of all NULLs = NULL, not 0
- COALESCE wraps fix the all-NULL edge case
NULL Behavior Per Function
Quick reference matrix
- COUNT(*) → includes NULLs in row count
- COUNT(col) → skips NULLs
- SUM(col) → skips NULLs; all NULL → NULL
- AVG(col) → skips NULLs in both numerator AND denominator
- MIN/MAX → skip NULLs; all NULL → NULL
Dangerous Patterns
NULL aggregate traps
- AVG with NULLs: denominator shrinks → average is higher than expected
- SUM after LEFT JOIN: unmatched rows add NULL, not 0 — use COALESCE
- COUNT(col) after LEFT JOIN: unmatched = 0, not NULL — correct by accident
- GROUP BY with NULL: all NULLs collapse into one group
Safe Patterns
NULL-proof aggregation
COALESCE(SUM(col), 0)— never get NULL totalCOALESCE(AVG(col), 0)— safe averageCOUNT(col)notCOUNT(*)after LEFT JOINSUM(CASE WHEN col IS NOT NULL THEN 1 ELSE 0 END)— explicit count
Interview Questions
What you'll be asked
- "What's the difference between COUNT(*) and COUNT(col)?"
- "What does AVG return if some values are NULL?"
- "How do you handle NULL in SUM after a LEFT JOIN?"
- "Does GROUP BY treat NULLs as equal?"
Syntax
Syntax Template
1COUNT(*) -- all rows, including NULL
2COUNT(col) -- non-NULL only
3AVG(COALESCE(col, 0)) -- treat NULL as 0
4SUM(col) -- ignores NULL
COUNT(*) vs COUNT(col)The key difference: * counts NULLs, column name does notCOALESCE(col, 0)Replaces NULL with 0 before aggregation Sample Data
reviews
12 rows
| id | rating | comment |
|---|---|---|
| 7001 | 5 | Excellent mouse, very comfy |
| 7002 | 4 | NULL |
| 7003 | 3 | Good but battery drains fast |
| 7004 | 5 | Solid build, worth every rupee |
| 7005 | 4 | Spacious — fits two monitors |
| 7006 | 4 | NULL |
| 7007 | 5 | Just works, durable braided cable |
| 7008 | 5 | Great sound for the price |
| 7009 | 4 | Battery could be better |
| 7010 | 3 | NULL |
| 7011 | 5 | Good notebooks, smooth paper |
| 7012 | 5 | Premium feel, noise cancellation is real |
products
10 rows
| id | name | category | price | stock |
|---|---|---|---|---|
| P101 | Wireless Mouse | Electronics | 24.99 | 150 |
| P102 | Office Desk | Furniture | 189 | 25 |
| P103 | LED Desk Lamp | Furniture | 45.5 | 80 |
| P104 | USB-C Cable | Electronics | 12.99 | 500 |
| P105 | Bluetooth Speaker | Electronics | 79 | 60 |
| P106 | Office Chair | Furniture | 95 | 15 |
| P107 | Coffee Maker | Appliances | 89.99 | 40 |
| P108 | Notebook Set | Stationery | 8.5 | 200 |
| P109 | Wireless Headphones | Electronics | 159 | 30 |
| P110 | Standing Desk | Furniture | 425 | 8 |
Worked Example
Compare COUNT(*) vs COUNT(comment) and show the percentage of reviews with comments.
SQL
1SELECT
2 COUNT(*) AS total,
3 COUNT(comment) AS with_comment,
4 ROUND(100.0 * COUNT(comment) / COUNT(*), 1) AS pct_with_comment
5FROM reviews;
COUNT(*) = 15 (all rows). COUNT(comment) = 13 (2 NULLs excluded). The percentage shows 86.7% of reviews have a comment. Note the 100.0 (not 100) to force floating-point division in SQLite.
Output
1 row
| total | with_comment | pct_with_comment |
|---|---|---|
| 12 | 9 | 75 |
Key Concepts
1COUNT(*) is the ONLY aggregate that counts NULLs
2AVG silently inflates results by excluding NULLs from the denominator
3Fix with COALESCE inside the aggregate: AVG(COALESCE(col, 0))
4NULL values form their own group in GROUP BY
Pro Tip
If you don't understand how NULLs interact with aggregates, your counts will be wrong, your averages skewed, and your reports silently misleading.
When to Use
Accurate average ratings, correct revenue counts excluding missing data, identifying incomplete records.
For each product category, show the count of products and the average price. Name the columns: category, count, avg_price.
Your Query