SQL NULL in Aggregate Functions

COUNT(column), SUM, AVG, MAX, and MIN all silently ignore NULL rows — except COUNT(*), which counts every row regardless.

What & Why

Every standard aggregate function — SUM, AVG, MAX, MIN, and COUNT(column) — simply skips NULL values when computing its result, as if those rows weren't part of the input at all. The one genuine exception is COUNT(*), which counts every row regardless of what any column contains.

See How It Works

BUSINESS QUESTION

Product wants several aggregates computed over the same nullable NPS score column.

id	user_id	feature_id	rating	nps_score	comment	created_at
1	101	1	5	9	Clear and useful	2024-01-25 12:15:00+00
2	102	2	4	NULL	Export worked well	2024-02-20 15:40:00+00
3	103	1	4	7	Helpful setup flow	2024-03-18 09:05:00+00
4	104	3	3	NULL	Needs clearer timing	2024-04-08 17:30:00+00

Watch each aggregate handle the same nullable columnStep 1 of 5

COUNT(*)

expression	result
COUNT(*)	4

COUNT(*) counts all four product.feedback rows, including the two rows whose NPS score is NULL.

EXAMPLE QUERY

SELECT
  COUNT(*) AS total_feedback,
  COUNT(nps_score) AS scored_feedback,
  SUM(nps_score) AS score_sum,
  ROUND(AVG(nps_score), 2) AS average_score,
  MAX(nps_score) AS max_score
FROM product.feedback;

Now You Try

Practice this concept

Product wants several aggregates computed over the same nullable NPS score column.

Available schema

product

Prefix tables with product.table_name.

total_feedbackscored_feedbackscore_sumaverage_scoremax_score

product.feedback

Column	Type
id	integer
user_id	integer
feature_id	integer
rating	integer
nps_score	integer
comment	text
created_at	timestamp with time zone
moderation_bucket	text

query.sql

Intermediate business practice