Aggregate with NULL handling in SQL - Step-by-Step Execution

Concept Flow - Aggregate with NULL handling

Start with dataset

↓

Apply aggregate function

↓

Check for NULL values

↓

Ignore NULLs

↓

Calculate result

↓

Return aggregate output

The aggregate function processes data rows, skips NULLs, then calculates and returns the result.

Execution Sample

SQL

SELECT AVG(score) FROM tests;
-- scores: 10, NULL, 20, 30
-- AVG ignores NULL and averages 10,20,30

Calculates average score ignoring NULL values in the 'score' column.

Execution Table

Step	Row Value	Is NULL?	Action	Running Sum	Count	Intermediate AVG
1	10	No	Include in sum and count	10	1	10.0
2	NULL	Yes	Ignore NULL	10	1	10.0
3	20	No	Include in sum and count	30	2	15.0
4	30	No	Include in sum and count	60	3	20.0
End	-	-	Calculate AVG = sum/count	60	3	20.0

💡 All rows processed; NULL values ignored in aggregate calculation.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
Running Sum	0	10	10	30	60	60
Count	0	1	1	2	3	3
Intermediate AVG	NULL	10.0	10.0	15.0	20.0	20.0

Key Moments - 2 Insights

Why does the NULL value not affect the average calculation?

Because the aggregate function AVG ignores NULLs, as shown in step 2 of the execution_table where NULL is skipped without changing sum or count.

What happens if all values are NULL?

The aggregate function returns NULL since count remains zero and no values are included, similar to how step 2 skips NULLs but if all were NULL, no sum or count would accumulate.

Visual Quiz - 3 Questions

Look at the execution_table, what is the Running Sum after processing the third row?

A 20

B 10

C 30

D 60

💡 Hint

Check the 'Running Sum' column at step 3 in the execution_table.

At which step does the aggregate function ignore a value due to NULL?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look for 'Is NULL?' = Yes in the execution_table.

If the second row was 15 instead of NULL, what would the final AVG be?

A 15

B 18.75

C 20

D 25

💡 Hint

Add 10 + 15 + 20 + 30 = 75, divide by 4 rows; check variable_tracker logic.

Concept Snapshot

Aggregate functions like AVG ignore NULL values.
They sum only non-NULLs and count those rows.
Final result = sum of values / count of non-NULLs.
If all values are NULL, result is NULL.
NULLs do not affect sum or count.

Full Transcript

This visual execution shows how SQL aggregate functions handle NULL values. Starting with a list of values including NULL, each row is checked. If the value is NULL, it is ignored and does not add to the sum or count. Non-NULL values add to the running sum and increase the count. The average is calculated by dividing the sum by the count of non-NULL values. This process ensures NULLs do not skew the aggregate result. If all values were NULL, the aggregate would return NULL. This step-by-step trace helps beginners understand how NULLs are handled in aggregates.