Does AVG ignore NULL in SQL? Yes, and it can make an average look better than it is

Yes. AVG uses only the rows where the value is not NULL, and it doesn't tell you how many it skipped. These are the first five refunds on Meridian, a fictional practice marketplace:

AVG over these five returns 42 hours, the average of the three completed refunds. The two open ones are simply not in it. On the whole table:

SELECT
  ROUND(AVG(EPOCH(completed_at - requested_at)) / 86400, 1) AS avg_days,
  COUNT(*) AS refunds,
  COUNT(completed_at) AS completed
FROM meridian.refunds;
-- result: 2.0 · 323 · 208

2.0 days is true for 208 refunds. The other 115 are still open, and 24 of them were requested before 15 September: by the time the data ends they have waited longer than any completed refund took, which was under 4 days.

Why AVG skips NULL

NULL means "unknown", not zero. AVG, SUM, MIN, MAX and COUNT(column) leave unknown values out; only COUNT(*) counts every row. (EPOCH() turns the time difference into seconds in DuckDB; in PostgreSQL it is EXTRACT(EPOCH FROM …).)

Skipping is often what you want. A product nobody has rated has no rating, and averaging only the rated products is fair, as long as you say so.

It misleads when NULL means "not finished yet". The refunds without a completed_at include the slowest ones, so leaving them out removes exactly the cases that would raise the average. Delivery times without the undelivered parcels, ticket resolution times without the open tickets and time to first purchase without the people who haven't bought yet all have the same problem.

COALESCE(x, 0) makes it worse

The usual "fix" is to replace NULL with something:

SELECT ROUND(AVG(EPOCH(COALESCE(completed_at, requested_at) - requested_at)) / 86400, 1)
FROM meridian.refunds;
-- result: 1.3

Now every open refund counts as done the moment it was requested, and the average drops to 1.3 days. The longer refunds wait, the better this number looks.

The same thing happens to ratings. In the Meridian catalogue 54 of 60 products have a rating, and AVG(rating) is 4.40. AVG(COALESCE(rating, 0)) is 3.96, as if six new products had been given zero stars. A missing value is not a zero.

What to report instead

1. The average with what it covers: "2.0 days for the 208 completed refunds; 115 are still open."
2. A share over requests old enough to judge, which has no NULL problem. Of the 142 refunds requested before 15 September, 87 (61%) were completed within 3 days and 24 are still open:

SELECT
  COUNT(*) AS requested,
  COUNT(*) FILTER (WHERE completed_at <= requested_at + INTERVAL 3 DAY) AS within_3_days,
  COUNT(*) - COUNT(completed_at) AS still_open
FROM meridian.refunds
WHERE requested_at < TIMESTAMP '2026-09-15';
-- result: 142 · 87 · 24

3. The open cases on their own line: how many, and how long the oldest has waited. That line is often the most useful one for whoever asked.

The habit: COUNT(column) next to COUNT(*)

Before any AVG, run SELECT COUNT(*), COUNT(the_column). If the two differ, decide what NULL means in this column before you average it:

Try it on a colleague's question

SELECT AVG(completed_at - requested_at)
FROM refunds;
-- about 2.0 days: 208 of 323 refunds have a completed_at

Learn it on day 1

Start, Atterna's SQL course from zero, shows you an empty cell on day 1, before you write any SQL: a scented candle with no rating in the product table. You decide what that empty cell means before a query does it for you. Start day 1. All 10 days of Start are free with an account, and days 1–2 need no account at all.

FAQ

Does AVG ignore NULL values in SQL?

Yes. AVG(column) adds up the non-NULL values and divides by how many there are, so rows with NULL are left out of both the sum and the count. It doesn't return NULL unless every value is NULL. Check COUNT(column) against COUNT(*) to see how many rows were skipped.

Does COUNT ignore NULL?

COUNT(column) does: it counts only the rows where the column has a value. COUNT(*) counts every row, NULL or not. The difference between the two is the number of NULLs in that column.

Should I replace NULL with 0 before averaging?

Only when NULL really means zero, for example a missing discount that means no discount. When NULL means unknown or not finished yet, replacing it with 0 invents data: on the Meridian refunds it turns 2.0 days into 1.3 days. Report what the average covers instead, or use a measure that includes the open cases.

How do I count NULL values in a column?

SELECT COUNT(*) - COUNT(the_column) FROM the_table gives the number of NULLs. Many databases also accept COUNT(*) FILTER (WHERE the_column IS NULL), or the portable SUM(CASE WHEN the_column IS NULL THEN 1 ELSE 0 END).