NULL behavior in aggregate functions in SQL

Introduction

NULL values represent missing or unknown data. Understanding how aggregate functions handle NULLs helps you get correct results when summarizing data.

When calculating the average salary but some employees have no salary recorded.

When counting the number of orders but some orders have missing customer IDs.

When summing sales amounts where some sales records have NULL values.

When finding the maximum score but some scores are not entered.

When grouping data and you want to know how NULLs affect totals or counts.

Syntax

SQL

AGGREGATE_FUNCTION(column_name)

-- Examples of aggregate functions:
-- COUNT(column_name), SUM(column_name), AVG(column_name), MAX(column_name), MIN(column_name)

Aggregate functions ignore NULL values except COUNT(*), which counts all rows.

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

Examples

Counts only rows where column_name is not NULL.

SQL

SELECT COUNT(column_name) FROM table;

Counts all rows, including those with NULLs.

SQL

SELECT COUNT(*) FROM table;

Calculates average ignoring NULL values in column_name.

SQL

SELECT AVG(column_name) FROM table;

Sums values ignoring NULLs in column_name.

SQL

SELECT SUM(column_name) FROM table;

Sample Program

This example shows how aggregate functions treat NULLs in the amount column.

SQL

CREATE TABLE sales (
  id INT,
  amount INT
);

INSERT INTO sales (id, amount) VALUES
(1, 100),
(2, NULL),
(3, 200),
(4, NULL);

SELECT COUNT(amount) AS count_amount,
       COUNT(*) AS count_all,
       SUM(amount) AS sum_amount,
       AVG(amount) AS avg_amount
FROM sales;

Important Notes

NULL values are skipped in SUM, AVG, MAX, MIN, and COUNT(column_name).

COUNT(*) counts all rows regardless of NULLs.

If all values are NULL, SUM and AVG return NULL.

Summary

Aggregate functions ignore NULLs except COUNT(*) which counts all rows.

COUNT(column) counts only non-NULL values.

SUM and AVG skip NULLs but return NULL if no non-NULL values exist.

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