0Pricing
SQL Interview Prep · Lesson

SUM and AVG with NULLs

Why AVG ignores NULLs and how that changes the answer interviewers expect.

SUM and AVG with NULLs is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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.

SUM Ignores NULLs

SUM(bonus) adds up only the non-NULL values: 100 + 200 + 300 = 600. The NULL row contributes nothing; it is simply skipped, not treated as zero in the arithmetic sense of altering the count.

The practical effect is the same as treating NULL as absent. SUM never errors on NULLs and never returns NULL unless every input is NULL.

SELECT SUM(bonus) AS total_bonus
FROM employees;
-- returns 600

AVG Ignores NULLs Too

AVG(bonus) is the crucial one. It computes sum of non-NULL values divided by the count of non-NULL values: 600 / 3 = 200.

The denominator is 3, not 4. The NULL row is excluded from both the numerator and the divisor. This is exactly why AVG can surprise people: the average is over present values, not over all rows.

SELECT AVG(bonus) AS avg_bonus
FROM employees;
-- 600 / 3 = 200, NOT 600 / 4 = 150

Why the Denominator Matters

Suppose the business meaning of NULL bonus is "received no bonus" = 0. Then the true average should be 600 / 4 = 150, but AVG(bonus) reports 200.

The right answer in an interview: "AVG ignores NULLs, so it averages over employees who have a bonus. If NULL means zero, I must convert NULLs to 0 first." Naming this difference is what earns the point.

Forcing NULLs to Zero With COALESCE

To average over all rows treating NULL as 0, wrap the column in COALESCE(bonus, 0). Now every row has a numeric value, so the denominator becomes 4.

This yields 600 / 4 = 150. The lesson: AVG(col) and AVG(COALESCE(col, 0)) answer different business questions. Choose deliberately.

SELECT AVG(COALESCE(bonus, 0)) AS avg_over_all
FROM employees;
-- 600 / 4 = 150

AVG = SUM / COUNT, Carefully

A useful identity: AVG(col) equals SUM(col) / COUNT(col) — note COUNT(col), not COUNT(*), because both AVG and that COUNT skip NULLs.

If you mistakenly write SUM(col) / COUNT(*), you get the over-all-rows average (150 here), which differs from AVG (200). Interviewers sometimes ask you to reconstruct AVG manually to see if you pick the right COUNT.

SELECT
  AVG(bonus)                       AS builtin_avg,   -- 200
  SUM(bonus) * 1.0 / COUNT(bonus)  AS manual_avg,    -- 200
  SUM(bonus) * 1.0 / COUNT(*)      AS over_all_rows  -- 150
FROM employees;

Integer Division Gotcha

A subtle bug when computing averages manually: in many databases dividing two integers does integer division, truncating the decimals. 7 / 2 can yield 3, not 3.5.

AVG itself usually returns a decimal, but if you rebuild it with SUM / COUNT on integer columns you may lose precision. Multiply by 1.0 or cast to a decimal type first.

SELECT
  SUM(bonus) / COUNT(bonus)        AS maybe_truncated,
  SUM(bonus) * 1.0 / COUNT(bonus)  AS precise
FROM employees;

When Everything Is NULL

Edge case interviewers love: what if every value is NULL, or the filter matches no rows?

  • SUM returns NULL (not 0) when there are no non-NULL inputs.
  • AVG returns NULL as well, since dividing by a zero count is undefined.
  • COUNT, by contrast, returns 0.

Wrap the result in COALESCE(SUM(col), 0) if you need a numeric default.

SELECT COALESCE(SUM(bonus), 0) AS safe_total
FROM employees
WHERE 1 = 0;  -- no rows: returns 0, not NULL

Per-Group Averages

The same NULL rules apply inside GROUP BY. Each group's AVG divides by that group's count of non-NULL values. A group made entirely of NULL bonuses yields AVG = NULL for that group.

So when you see surprising per-department averages, suspect NULLs shrinking individual denominators before you suspect a join bug.

SELECT department, AVG(bonus) AS avg_bonus
FROM employees
GROUP BY department;

How to Phrase the Answer

A polished interview answer sounds like this: "SUM and AVG both ignore NULLs. AVG divides by the count of non-NULL values, so NULLs effectively shrink the denominator. If NULL should count as zero, I convert it with COALESCE before aggregating; otherwise the average reflects only rows that have a value."

That one sentence demonstrates correctness, business awareness, and the fix.

Quick Check

Apply the rule to the sample data.

Recap

Key takeaways on SUM and AVG with NULLs:

  • Both ignore NULLs entirely.
  • AVG(col) = SUM(col) / COUNT(col) — the denominator excludes NULLs.
  • Use COALESCE(col, 0) when NULL means zero and should count.
  • All-NULL or no-row inputs make SUM and AVG return NULL (COUNT returns 0).
  • Beware integer division when rebuilding AVG manually.

Next: MIN, MAX, and aggregating non-numeric data.

Frequently asked questions

Is the “SUM and AVG with NULLs” lesson free?

Yes — the full text of “SUM and AVG with NULLs” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “SUM and AVG with NULLs”?

Why AVG ignores NULLs and how that changes the answer interviewers expect. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “SUM and AVG with NULLs” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)
  2. SUM and AVG with NULLs
  3. MIN, MAX and Non-Numeric Aggregation
  4. Aggregates Without GROUP BY
← Back to SQL Interview Prep