Is this the same problem as SUM returning NULL?

No. SUM over an all-NULL column returns NULL, which is easy to spot. AVG over a mixed column returns a normal-looking number that answers a different question than the one you asked.

When is skipping NULL the right behavior?

When NULL means unknown or not applicable: average rating across products that have a rating, average time to first reply across tickets that got one. The answer is then 'average among those that have one' and should be labeled that way.

When is COALESCE(col, 0) the right fix?

When NULL really means zero: no discount, no refund, no tip. Not when it means unknown. A missing age counted as 0 drags the average down, which is the same trap as treating unset as active.

Does this differ by database?

NULL handling is the same in Postgres, MySQL and SQL Server. One difference: SQL Server's AVG on an integer column returns an integer, so 2.5 comes back as 2. Cast to a decimal type first.

What does AVG return when no rows match?

NULL, not 0. An empty filter shows a blank, so check the row count before reading anything into it.

AVG Ignores NULL | Average Too High

You ask: "What's the average discount per order?" The AI answers $15.00. Your orders are mostly full price. Something is off.

AVG(discount) skipped every order where discount is NULL. It averaged the discounted orders only.

AVG divides by the number of values, not rows

-- average over orders that HAVE a discount
SELECT AVG(discount) FROM orders

-- average over EVERY order, NULL meaning zero
SELECT AVG(COALESCE(discount, 0)) FROM orders

-- the same thing, spelled out
SELECT SUM(discount) * 1.0 / COUNT(*) FROM orders

Say 1,000 orders, 200 of them discounted at an average of $15. The first query returns $15. The second returns $3. Both are correct answers to different questions. Neither is flagged as the one you meant.

This is not SUM returning NULL, which fails loudly with a blank. It is the aggregate cousin of COUNT(column) being lower than COUNT(*): AVG(x) is SUM(x) / COUNT(x), and COUNT(x) does not count NULL.

Don't reach for COALESCE blindly

Whether to turn NULL into 0 depends on what NULL means in your table. For a discount, no value means zero. For a customer rating, no value means "never rated." Counting those as 0 would drag the average below the lowest real rating. COALESCE can also invent a status when it sits in a filter. Ask what the empty value means before you pick the denominator.

How to catch it

SELECT COUNT(*) AS total_rows,
       COUNT(discount) AS rows_with_value,
       AVG(discount) AS avg_of_those
FROM orders
-- if rows_with_value is far below total_rows,
-- the average only covers the rows that have a value

If the AI reports an average, ask for both counts next to it.

The prompt that heads this off

When you report an average, also show how many rows it covers and how many
rows matched the filter. If some values are NULL, say whether you treated
them as zero or excluded them, and why.

The short version

AVG ignores NULL. "Average per order" and "average among orders that have one" are different numbers, and the SQL runs either way. Check the two counts before you quote the average.

Read-only MCP gateway: mcpserver.design.

Related reading: