Aggregate with NULL handling in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What happens to NULL values when using the COUNT(column) aggregate function in SQL?

COUNT(column) counts only non-NULL values in the specified column. NULL values are ignored.

beginner

How does the SUM() function treat NULL values in SQL?

SUM() ignores NULL values and adds only the non-NULL numeric values.

intermediate

What is the difference between COUNT(*) and COUNT(column) in handling NULLs?

COUNT(*) counts all rows regardless of NULLs, while COUNT(column) counts only rows where the column is NOT NULL.

intermediate

How can you include NULL values as zero in aggregate calculations like SUM or AVG?

Use COALESCE(column, 0) to replace NULLs with zero before aggregation, e.g., SUM(COALESCE(column, 0)).

advanced

Why is it important to handle NULLs explicitly in aggregate queries?

Because NULLs can cause aggregates like AVG or SUM to return unexpected results or skip data, handling NULLs ensures accurate calculations.

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

A COUNT(*)

B COUNT(column)

C SUM(column)

D AVG(column)

COUNT(*) counts every row regardless of NULLs in any column.

What does SUM(column) do when the column contains NULL values?

A Returns NULL if any NULL exists

B Ignores NULLs and sums non-NULL values

C Counts NULLs as zero automatically

D Throws an error

SUM ignores NULL values and adds only the non-NULL numbers.

How can you count only the rows where a column is NOT NULL?

A COUNT(*)

B SUM(column)

C COUNT(column)

D AVG(column)

COUNT(column) counts only rows where the column is NOT NULL.

Which SQL function can replace NULL with a specific value before aggregation?

A IFNULL()

B COALESCE()

C NVL()

D All of the above

IFNULL, COALESCE, and NVL are functions used in different SQL dialects to replace NULLs.

What will AVG(column) return if all values in the column are NULL?

A NULL

B 0

C Error

D 1

AVG returns NULL if there are no non-NULL values to average.

Explain how NULL values affect aggregate functions like COUNT, SUM, and AVG in SQL.

Think about which aggregates count or sum NULLs and which do not.

Describe a method to include NULL values as zero in aggregate calculations and why it might be useful.

Consider how to treat missing data as zero for accurate sums or averages.

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