NULL behavior in aggregate functions in SQL - Step-by-Step Execution

Concept Flow - NULL behavior in aggregate functions

Start with data set

↓

Check each value

↓

Is value NULL?

Yes→Skip value

No↓

Include value in aggregate

↓

Calculate aggregate result

↓

Return final result

Aggregate functions process each value, skipping NULLs, then compute the final result from non-NULL values.

Execution Sample

SQL

SELECT COUNT(column), SUM(column), AVG(column) FROM table;

This query counts non-NULL values, sums non-NULL values, and averages non-NULL values in 'column'.

Execution Table

Step	Value Checked	Is NULL?	Action	Aggregate State	Output
1	10	No	Include in aggregates	COUNT=1, SUM=10, AVG=10
2	NULL	Yes	Skip value	COUNT=1, SUM=10, AVG=10
3	20	No	Include in aggregates	COUNT=2, SUM=30, AVG=15
4	NULL	Yes	Skip value	COUNT=2, SUM=30, AVG=15
5	30	No	Include in aggregates	COUNT=3, SUM=60, AVG=20
6	End of data			Final aggregates	COUNT=3, SUM=60, AVG=20

💡 All values processed; NULLs skipped; aggregates computed from non-NULL values.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
COUNT	0	1	1	2	2	3	3
SUM	0	10	10	30	30	60	60
AVG	NULL	10	10	15	15	20	20

Key Moments - 3 Insights

Why does COUNT count only 3 values when there are 5 rows?

COUNT ignores NULL values, so only non-NULL values are counted as shown in execution_table rows 1, 3, and 5.

Why does SUM not include NULL values in its total?

SUM skips NULLs and adds only non-NULL values, as seen in execution_table where NULLs at steps 2 and 4 are skipped.

How is AVG calculated when some values are NULL?

AVG divides the sum of non-NULL values by the count of non-NULL values, ignoring NULLs, as shown in variable_tracker for AVG.

Visual Quiz - 3 Questions

Look at the execution_table, what is the COUNT after step 3?

A 3

B 2

C 1

D 0

💡 Hint

Check the 'Aggregate State' column at step 3 in the execution_table.

At which step does the SUM become 30?

A Step 3

B Step 1

C Step 5

D Step 2

💡 Hint

Look at the 'SUM' value in the 'Aggregate State' column for each step in the execution_table.

If NULL values were counted in COUNT, what would be the final COUNT?

A 3

B 2

C 5

D 0

💡 Hint

Count total rows including NULLs from the execution_table steps.

Concept Snapshot

Aggregate functions like COUNT, SUM, AVG ignore NULL values.
COUNT counts only non-NULL rows.
SUM adds only non-NULL values.
AVG divides sum of non-NULL values by their count.
NULLs do not affect the aggregate result except COUNT(*) counts all rows.

Full Transcript

When using aggregate functions in SQL, NULL values are ignored in calculations. For example, COUNT(column) counts only rows where the column is not NULL. SUM(column) adds only the non-NULL values. AVG(column) calculates the average of non-NULL values by dividing the sum of those values by their count. This means NULLs do not contribute to the total or average but are simply skipped. The execution table shows each value checked, whether it is NULL, and how the aggregates update only when the value is not NULL. This behavior helps avoid errors and ensures aggregates reflect only meaningful data.