NULL behavior in aggregate functions in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of COUNT with NULL values

Consider a table Employees with a column Bonus that contains some NULL values. What is the output of the following query?

SELECT COUNT(Bonus) FROM Employees;

SQL

CREATE TABLE Employees (ID INT, Bonus INT);
INSERT INTO Employees VALUES (1, 100), (2, NULL), (3, 200), (4, NULL);

A 2

B 4

C 0

D NULL

💡 Hint

COUNT(column) counts only non-NULL values.

COUNT(column) counts only rows where the column is NOT NULL. Here, Bonus has 2 non-NULL values (100 and 200).

❓ query_result

intermediate

SUM ignoring NULL values

Given a table Sales with a column Amount containing some NULLs, what is the output of this query?

SELECT SUM(Amount) FROM Sales;

SQL

CREATE TABLE Sales (ID INT, Amount INT);
INSERT INTO Sales VALUES (1, 50), (2, NULL), (3, 150), (4, NULL);

A NULL

B 200

C 0

D 50

💡 Hint

SUM ignores NULL values and sums only non-NULL entries.

SUM adds only non-NULL values: 50 + 150 = 200.

🧠 Conceptual

advanced

Behavior of AVG with NULL values

Which statement correctly describes how the AVG aggregate function treats NULL values in a column?

A AVG includes NULL values as zero in the calculation.

B AVG returns NULL if any NULL values exist in the column.

C AVG ignores NULL values and calculates the average of non-NULL values only.

D AVG counts NULL values as one in the denominator.

💡 Hint

Think about how average is calculated when some values are missing.

AVG ignores NULLs and calculates the average only over non-NULL values.

❓ query_result

advanced

MIN function with all NULL values

What is the output of this query when all values in the Score column are NULL?

SELECT MIN(Score) FROM Results;

SQL

CREATE TABLE Results (ID INT, Score INT);
INSERT INTO Results VALUES (1, NULL), (2, NULL), (3, NULL);

A Minimum integer value

B 0

C Error

D NULL

💡 Hint

MIN returns NULL if no non-NULL values exist.

MIN returns NULL when all values are NULL because there is no minimum value to find.

❓ query_result

expert

Aggregate functions and NULL in WHERE clause

Consider a table Orders with a column Discount that contains some NULL values. What is the output of the following query?

SELECT AVG(Discount) FROM Orders WHERE Discount > 10;

SQL

CREATE TABLE Orders (ID INT, Discount INT);
INSERT INTO Orders VALUES (1, 5), (2, NULL), (3, 20), (4, NULL), (5, 10), (6, 30);

A 25

B NULL

C Error

D 0

💡 Hint

NULL > 10 evaluates to UNKNOWN (not true), so rows with NULL are excluded. AVG is then computed on qualifying non-NULL values.

WHERE Discount > 10 selects rows with 20 and 30. AVG(20, 30) = 25. NULL values do not cause an error; they are simply filtered out.

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