Aggregate with NULL handling in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the result of this query with NULLs in aggregation?

Consider a table sales with columns region and amount. Some amount values are NULL.
What is the output of this query?

SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region;

SQL

CREATE TABLE sales (region VARCHAR(10), amount INT);
INSERT INTO sales VALUES
('North', 100), ('North', NULL), ('South', 200), ('South', NULL), ('East', NULL);

A [{"region": "North", "total_sales": 100}, {"region": "South", "total_sales": 200}]

B [{"region": "North", "total_sales": 100}, {"region": "South", "total_sales": 200}, {"region": "East", "total_sales": 0}]

C [{"region": "North", "total_sales": 100}, {"region": "South", "total_sales": 200}, {"region": "East", "total_sales": null}]

D [{"region": "North", "total_sales": null}, {"region": "South", "total_sales": null}, {"region": "East", "total_sales": null}]

💡 Hint

SUM ignores NULL values and returns NULL only if all values are NULL for that group.

The SUM aggregate function skips NULLs. For 'North' and 'South', there are non-NULL amounts, so sums are 100 and 200 respectively. For 'East', all amounts are NULL, so SUM returns NULL.

❓ query_result

intermediate

How does COUNT(*) differ from COUNT(column) with NULLs?

Given a table employees with a column manager_id that can be NULL, what is the output of this query?

SELECT COUNT(*), COUNT(manager_id) FROM employees;

SQL

CREATE TABLE employees (id INT, manager_id INT);
INSERT INTO employees VALUES (1, NULL), (2, 1), (3, NULL), (4, 2);

A [4, 2]

B [2, 4]

C [4, 4]

D [2, 2]

💡 Hint

COUNT(*) counts all rows, COUNT(column) counts only non-NULL values in that column.

COUNT(*) counts all 4 rows. COUNT(manager_id) counts only rows where manager_id is not NULL, which are 2 rows.

📝 Syntax

advanced

Which query correctly replaces NULLs with zero before aggregation?

You want to sum the score column but treat NULL as zero. Which query is correct?

A SELECT SUM(score) WHERE score IS NOT NULL OR 0 FROM results;

B SELECT SUM(NULLIF(score, 0)) FROM results;

C SELECT SUM(IFNULL(score)) FROM results;

D SELECT SUM(COALESCE(score, 0)) FROM results;

💡 Hint

Use a function that replaces NULL with a value before summing.

COALESCE(score, 0) replaces NULL with 0, so SUM adds zeros instead of ignoring NULLs. NULLIF returns NULL if score=0, which is not desired. IFNULL requires two arguments. The WHERE clause syntax is invalid.

🔧 Debug

advanced

Why does this query return NULL instead of zero?

Given this query:

SELECT AVG(discount) FROM orders WHERE discount > 0;

Some rows have NULL in discount. The result is NULL. Why?

SQL

CREATE TABLE orders (id INT, discount INT);
INSERT INTO orders VALUES (1, NULL), (2, 0), (3, NULL);

A No rows satisfy discount > 0, so AVG returns NULL.

B The WHERE clause is invalid and causes NULL result.

C AVG returns NULL if any discount is NULL in the table.

D AVG cannot be used with NULL values in any rows.

💡 Hint

Check how many rows meet the WHERE condition.

The WHERE discount > 0 filters out all rows because none have discount > 0. AVG of zero rows returns NULL.

🧠 Conceptual

expert

Which aggregate function returns the count of distinct non-NULL values?

You want to count how many unique non-NULL values exist in a column category. Which aggregate function achieves this?

A SUM(DISTINCT category)

B COUNT(DISTINCT category)

C COUNT(category)

D COUNT(*)

💡 Hint

Think about counting unique values excluding NULLs.

COUNT(DISTINCT category) counts unique non-NULL values. SUM(DISTINCT category) is invalid for non-numeric or may sum distinct values but not count them. COUNT(category) counts all non-NULL values including duplicates. COUNT(*) counts all rows.

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