NULL Behavior in Aggregates
All aggregate functions except COUNT(*) ignore NULL values automatically. This affects your totals and averages in subtle ways. Understanding how NULLs interact with aggregates prevents hidden bugs in reports.
NULL Aggregate Rules
- COUNT(*) - counts all rows including NULL rows
- COUNT(col) - skips rows where col is NULL
- SUM(col) - ignores NULLs, adds only non-NULL values
- AVG(col) - ignores NULLs in both numerator and denominator
- MIN/MAX - ignore NULLs
- SUM of all NULLs = NULL (not 0)
- Use COALESCE or IFNULL to treat NULLs as 0 if needed
NULL Aggregate Examples
-- payments table: paid_at is NULL for unpaid payments
SELECT COUNT(*) AS total_payments FROM payments; -- 2
SELECT COUNT(paid_at) AS confirmed_payments FROM payments; -- may differ if paid_at is NULL
-- SUM ignores NULLs - only sums non-NULL amounts
SELECT SUM(amount) FROM payments; -- sums only existing rows
-- AVG ignores NULLs - denominator is non-NULL count
-- If 5 rows exist but 2 have NULL paid_at,
-- COUNT(*) = 5, COUNT(paid_at) = 3
-- Treat NULL as 0 in SUM
SELECT SUM(COALESCE(amount, 0)) AS total FROM payments;
-- Treat NULL as 0 in AVG (divides by ALL rows)
SELECT
SUM(COALESCE(amount, 0)) / COUNT(*) AS avg_including_nulls,
AVG(amount) AS avg_excluding_nulls
FROM payments;
-- Check for NULL impact
SELECT
COUNT(*) AS all_rows,
COUNT(paid_at) AS has_paid_at,
COUNT(*) - COUNT(paid_at) AS null_count
FROM payments;Quick Quiz
Tip
Tip
Practice NULL Behavior in Aggregates in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Inner query runs first, results used by outer query. CTEs are often more readable.
Common Mistake
Warning
A common mistake with NULL Behavior in Aggregates is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of NULL Behavior in Aggregates from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- All aggregate functions except COUNT(*) ignore NULL values automatically.
- COUNT(*) - counts all rows including NULL rows
- COUNT(col) - skips rows where col is NULL
- SUM(col) - ignores NULLs, adds only non-NULL values