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.

Practice

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

easy

A. COUNT(*)

B. COUNT(column_name)

C. SUM(column_name)

D. AVG(column_name)

Solution

1. Step 1: Understand COUNT(*) behavior

   COUNT(*) counts every row in the table regardless of NULLs.

2. Step 2: Compare with other aggregate functions

   COUNT(column_name) counts only non-NULL values; SUM and AVG ignore NULLs but do not count rows.

3. Final Answer:

   COUNT(*) -> Option A

4. Quick Check:

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

Hint: COUNT(*) counts all rows, NULL or not [OK]

Common Mistakes:

• Thinking COUNT(column) counts NULLs
• Assuming SUM counts NULLs as zero
• Confusing AVG with COUNT(*)

2. Which of the following is the correct syntax to count only non-NULL values in the column salary?

easy

A. SELECT COUNT(salary) FROM employees;

B. SELECT COUNT(*) FROM employees;

C. SELECT COUNT(salary) FROM employees WHERE salary IS NULL;

D. SELECT COUNT(*) FROM employees WHERE salary IS NULL;

Solution

1. Step 1: Understand COUNT(column) syntax

   COUNT(column) counts only non-NULL values in that column automatically.

2. Step 2: Analyze other options

   SELECT COUNT(*) FROM employees; counts all rows including those with NULL salary; SELECT COUNT(salary) FROM employees WHERE salary IS NULL; counts non-NULL salaries where salary is NULL (returns 0); SELECT COUNT(*) FROM employees WHERE salary IS NULL; counts rows with NULL salary.

3. Final Answer:

   SELECT COUNT(salary) FROM employees; -> Option A

4. Quick Check:

   COUNT(column) counts non-NULL values [OK]

Hint: Use COUNT(column) to count non-NULL values directly [OK]

Common Mistakes:

• Using WHERE clause unnecessarily with COUNT(column)
• Confusing COUNT(*) with COUNT(column)
• Counting NULL values by mistake

3. Given the table sales with column amount containing values (100, NULL, 200, NULL), what is the result of SELECT SUM(amount) FROM sales;?

medium

A. Syntax Error

B. NULL

C. 0

D. 300

Solution

1. Step 1: Identify values in amount column

   Values are 100, NULL, 200, NULL.

2. Step 2: Understand SUM behavior with NULLs

   SUM ignores NULLs and adds only 100 + 200 = 300.

3. Final Answer:

   300 -> Option D

4. Quick Check:

   SUM ignores NULLs and sums non-NULLs [OK]

Hint: SUM skips NULLs, adds only real numbers [OK]

Common Mistakes:

• Assuming SUM returns NULL if any NULL present
• Confusing SUM with COUNT
• Expecting 0 instead of sum of non-NULLs

4. Consider this query: SELECT AVG(score) FROM tests; where all score values are NULL. What is the issue and how to fix it?

medium

A. AVG returns 0; fix by casting scores to integers

B. AVG returns NULL; fix by filtering NULLs with WHERE score IS NOT NULL

C. Syntax error; fix by adding GROUP BY

D. AVG returns NULL; fix by using COUNT(score) instead

Solution

1. Step 1: Understand AVG behavior with all NULLs

   AVG returns NULL if no non-NULL values exist.

2. Step 2: Fix by filtering NULLs

   Use WHERE score IS NOT NULL to exclude NULLs before averaging.

3. Final Answer:

   AVG returns NULL; fix by filtering NULLs with WHERE score IS NOT NULL -> Option B

4. Quick Check:

   AVG returns NULL if no non-NULL values [OK]

Hint: Filter NULLs before AVG to avoid NULL result [OK]

Common Mistakes:

• Expecting AVG to return 0 with all NULLs
• Trying to fix with GROUP BY unnecessarily
• Using COUNT instead of AVG to get average

5. You want to find the average salary of employees but return 0 if all salaries are NULL. Which query achieves this?

hard

A. SELECT SUM(salary)/COUNT(*) FROM employees;

B. SELECT AVG(COALESCE(salary, 0)) FROM employees;

C. SELECT COALESCE(AVG(salary), 0) FROM employees;

D. SELECT COUNT(salary) FROM employees WHERE salary IS NOT NULL;

Solution

1. Step 1: Understand AVG with all NULL salaries

   AVG returns NULL if no non-NULL salaries exist.

2. Step 2: Use COALESCE to replace NULL with 0

   COALESCE(AVG(salary), 0) returns 0 instead of NULL when AVG is NULL.

3. Step 3: Analyze other options

   SELECT AVG(COALESCE(salary, 0)) FROM employees; averages zeros which skews result; SELECT SUM(salary)/COUNT(*) FROM employees; divides by total rows including NULLs; SELECT COUNT(salary) FROM employees WHERE salary IS NOT NULL; counts non-NULL salaries only.

4. Final Answer:

   SELECT COALESCE(AVG(salary), 0) FROM employees; -> Option C

5. Quick Check:

   Use COALESCE to handle NULL AVG results [OK]

Hint: Wrap AVG with COALESCE to replace NULL with zero [OK]

Common Mistakes:

• Replacing NULL salaries with zero before AVG skews average
• Dividing SUM by total rows including NULLs
• Using COUNT instead of AVG for average