SUM and AVG with NULLs
Why AVG ignores NULLs and how that changes the answer interviewers expect.
The Trap Hiding in AVG
Here is an interview classic that sinks careless candidates: "You have a salary column with some NULLs. What does AVG(salary) compute, and is it what the business wants?"
The honest answer reveals whether you understand that aggregates ignore NULLs, which changes the denominator of an average. Get this wrong in production and your reported average is silently inflated.
Let's make the behavior unmistakable.
Sample Data
Use this employees table with a nullable bonus column throughout the lesson:
- Alice, bonus 100
- Bob, bonus 200
- Carol, bonus NULL
- Dan, bonus 300
Four rows, three non-NULL bonuses, one NULL. We will run SUM and AVG against this and watch how NULL is treated.