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.