Aggregate with NULL handling in SQL

Introduction

Aggregates help us summarize data, but NULL values can hide or change results. Handling NULLs correctly ensures accurate summaries.

When calculating the average price of products but some prices are missing.

When counting how many people attended an event but some attendance records are unknown.

When summing sales amounts where some sales data is not recorded.

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

Syntax

SQL

SELECT AGGREGATE_FUNCTION(column_name) FROM table_name;

Common aggregate functions: COUNT, SUM, AVG, MAX, MIN.

By default, most aggregates ignore NULL values except COUNT(*).

Examples

Counts only non-NULL values in the column.

SQL

SELECT COUNT(column_name) FROM table_name;

Counts all rows, including those with NULLs.

SQL

SELECT COUNT(*) FROM table_name;

Calculates average ignoring NULL values.

SQL

SELECT AVG(column_name) FROM table_name;

Replaces NULL with 0 before summing to include all rows.

SQL

SELECT SUM(COALESCE(column_name, 0)) FROM table_name;

Sample Program

This example shows how aggregate functions treat NULLs differently. COUNT(amount) ignores NULLs, COUNT(*) counts all rows, AVG(amount) ignores NULLs, and SUM uses COALESCE to treat NULL as zero.

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_non_null,
       COUNT(*) AS count_all,
       AVG(amount) AS average_amount,
       SUM(COALESCE(amount, 0)) AS sum_with_nulls_handled
FROM sales;

Important Notes

COALESCE(column, value) replaces NULL with the given value for calculations.

COUNT(column) counts only rows where column is not NULL.

COUNT(*) counts all rows regardless of NULLs.

Summary

Aggregate functions summarize data but usually ignore NULLs.

Use COALESCE to include NULLs as a specific value in calculations.

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

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