How do aggregate functions handle NULL values in SQL?

How to think about it

What the question is really testing

This is a data integrity question dressed up as a SQL question. The interviewer wants to know whether you automatically check NULL rates before trusting an AVG, and whether you know when to substitute NULLs before aggregating versus when to leave them as-is.

The rule — all aggregates ignore NULLs except COUNT(*)

Every aggregate function — SUM, AVG, MIN, MAX, COUNT(col) — skips NULL rows entirely. Only COUNT(*) counts every row regardless.

scores = (80, 90, NULL, NULL, NULL)

COUNT(*)    = 5   -- counts every row
COUNT(score)= 2   -- only non-NULL rows
SUM(score)  = 170 -- NULLs skipped
AVG(score)  = 85  -- 170 / 2, not 170 / 5

Whether AVG = 85 is correct depends entirely on domain: if NULL means “did not attempt,” you probably want the denominator to be 2. If NULL means “scored zero,” you need to substitute before averaging.

Try it live — spot the hidden AVG skew

How to defend against the skew

Always compare COUNT(*) to COUNT(col) before trusting an AVG:

SELECT
  COUNT(*)        AS total_rows,
  COUNT(score)    AS non_null_rows,
  ROUND(100.0 * COUNT(score) / COUNT(*), 1) AS pct_filled,
  AVG(score)      AS avg_score
FROM exam_results;

If pct_filled is 40%, your average is based on less than half the data — that might be fine or it might be a data quality alarm.

Substitute when domain says NULL = zero:

AVG(COALESCE(score, 0))

Leave NULLs as-is when NULL means “not applicable”:

If a customer never made a purchase, avg_order_value should be NULL for them — not zero. Don’t blindly COALESCE without understanding the semantics.

NULL in GROUP BY

NULL values in the grouping column form their own group — all rows with a NULL key are bucketed together and appear as a NULL label in the result:

SELECT region, COUNT(*) FROM sales GROUP BY region;
-- A row with region = NULL forms its own group labelled NULL

MIN and MAX with all-NULL columns

If every value in the column is NULL, MIN and MAX return NULL — not an error. This is a common cause of confusing “successful” queries that produce NULL outputs.