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.

Practice

1. Which aggregate function counts all rows including those with NULL values in any column?

easy

A. COUNT(column_name)

B. COUNT(*)

C. SUM(column_name)

D. AVG(column_name)

Solution

1. Step 1: Understand COUNT(*) behavior

   COUNT(*) counts every row in the table regardless of NULL values in any column.

2. Step 2: Compare with COUNT(column_name)

   COUNT(column_name) counts only rows where the specified column is NOT NULL.

3. Final Answer:

   COUNT(*) -> Option B

4. Quick Check:

   COUNT(*) counts all rows including NULLs [OK]

Hint: Use COUNT(*) to count all rows including NULLs [OK]

Common Mistakes:

• Thinking COUNT(column) counts all rows
• Confusing SUM with COUNT
• Assuming AVG counts NULLs

2. Which SQL expression correctly replaces NULL values with zero before summing a column sales?

easy

A. SUM(NULLIF(sales, 0))

B. SUM(sales)

C. SUM(COALESCE(sales, 0))

D. SUM(ISNULL(sales))

Solution

1. Step 1: Understand COALESCE usage

   COALESCE(sales, 0) replaces NULL values in sales with 0 before summing.

2. Step 2: Check other options

   SUM(sales) ignores NULLs, NULLIF returns NULL if sales=0, ISNULL(sales) is incomplete syntax.

3. Final Answer:

   SUM(COALESCE(sales, 0)) -> Option C

4. Quick Check:

   Use COALESCE to replace NULLs before aggregation [OK]

Hint: Use COALESCE(column, 0) to treat NULL as zero in sums [OK]

Common Mistakes:

• Using SUM(sales) and expecting NULLs counted as zero
• Confusing NULLIF with COALESCE
• Using ISNULL without second argument

3. Given the table orders with column discount containing values (10, NULL, 10, NULL, 15), what is the result of this query?

SELECT AVG(COALESCE(discount, 0)) FROM orders;

medium

A. 7

B. 10

C. 15

D. NULL

Solution

1. Step 1: Replace NULLs with 0 using COALESCE

   Values become 10, 0, 10, 0, 15.

2. Step 2: Calculate average of these values

   Sum = 10 + 0 + 10 + 0 + 15 = 35; Count = 5; Average = 35 / 5 = 7.

3. Final Answer:

   7 -> Option A

4. Quick Check:

   COALESCE replaces NULLs, AVG includes zeros [OK]

Hint: Replace NULLs with zero before AVG to include them [OK]

Common Mistakes:

• Ignoring NULLs and averaging only non-NULL values
• Assuming AVG ignores zeros
• Miscounting number of rows

4. Identify the error in this query that tries to count all rows including NULLs in score column:

SELECT COUNT(score) + COUNT(NULL) FROM results;

medium

A. The query sums counts correctly

B. COUNT(score) counts all rows including NULLs

C. COUNT(NULL) counts NULLs as 1

D. COUNT(NULL) returns 0

Solution

1. Step 1: Understand COUNT(NULL) behavior

   COUNT(NULL) always returns 0 because the NULL expression is always NULL and thus never counted.

2. Step 2: Analyze COUNT(score)

   COUNT(score) counts only non-NULL values in score column, not all rows.

3. Final Answer:

   COUNT(NULL) returns 0 -> Option D

4. Quick Check:

   COUNT(NULL) always returns 0 [OK]

Hint: COUNT(NULL) always returns zero, use COUNT(*) for all rows [OK]

Common Mistakes:

• Thinking COUNT(NULL) counts NULLs
• Assuming COUNT(column) counts NULLs
• Adding COUNT(NULL) to count rows

5. You have a table employees with a nullable bonus column. You want to calculate the total bonus, treating NULL as zero, but only for employees with a salary above 50000. Which query correctly does this?

hard

A. SELECT SUM(COALESCE(bonus, 0)) FROM employees WHERE salary > 50000;

B. SELECT SUM(bonus) FROM employees WHERE COALESCE(salary, 0) > 50000;

C. SELECT SUM(COALESCE(bonus, 0)) WHERE salary > 50000 FROM employees;

D. SELECT SUM(bonus) FROM employees WHERE salary > 50000;

Solution

1. Step 1: Use COALESCE to treat NULL bonus as zero

   SUM(COALESCE(bonus, 0)) replaces NULL bonuses with 0 before summing.

2. Step 2: Filter employees with salary > 50000

   The WHERE clause correctly filters rows before aggregation.

3. Step 3: Check query syntax

   SELECT SUM(COALESCE(bonus, 0)) FROM employees WHERE salary > 50000; has correct syntax.

4. Final Answer:

   SELECT SUM(COALESCE(bonus, 0)) FROM employees WHERE salary > 50000; -> Option A

5. Quick Check:

   Use COALESCE in SUM and filter with WHERE [OK]

Hint: Use COALESCE in SUM and filter rows with WHERE [OK]

Common Mistakes:

• Placing WHERE clause after FROM incorrectly
• Not using COALESCE to handle NULLs
• Filtering on COALESCE(salary, 0) unnecessarily