Aggregate with NULL handling in SQL - Mini Project: Build & Apply

Aggregate with NULL handling

📖 Scenario: You work for a small bookstore that keeps track of book sales. Some sales records have missing values for the number of copies sold because the data was not recorded properly.Your task is to write SQL queries that calculate the total and average number of copies sold, correctly handling the missing (NULL) values.

🎯 Goal: Create a SQL query that calculates the total and average copies sold from the sales table, ignoring NULL values so the results are accurate.

📋 What You'll Learn

Create a table called sales with columns book_id (integer) and copies_sold (integer, can be NULL).

Insert the exact rows: (1, 10), (2, NULL), (3, 5), (4, NULL), (5, 15).

Write a query to calculate the total copies sold using SUM that ignores NULL values.

Write a query to calculate the average copies sold using AVG that ignores NULL values.

💡 Why This Matters

🌍 Real World

Handling NULL values in sales or inventory data is common in real businesses. Accurate aggregation helps managers make better decisions.

💼 Career

Database developers and analysts often write queries that must correctly handle missing data to produce reliable reports.

1

Create the sales table and insert data

Create a table called sales with columns book_id as integer and copies_sold as integer that can be NULL. Then insert these exact rows: (1, 10), (2, NULL), (3, 5), (4, NULL), (5, 15).

SQL

-- Create the sales table and insert the rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows. Remember that copies_sold can be NULL.

2

Add a variable for filtering NULL values

Add a variable or helper expression called non_null_sales that selects only rows from sales where copies_sold is not NULL.

SQL

CREATE TABLE sales (
  book_id INTEGER,
  copies_sold INTEGER
);

INSERT INTO sales (book_id, copies_sold) VALUES
(1, 10),
(2, NULL),
(3, 5),
(4, NULL),
(5, 15);

-- Define non_null_sales as rows where copies_sold is not NULL
-- Your code here

Hint

Use a WITH clause to create a temporary named result set filtering out NULLs.

3

Calculate total copies sold ignoring NULLs

Write a SQL query that uses SUM(copies_sold) on the non_null_sales to calculate the total copies sold, ignoring NULL values.

SQL

CREATE TABLE sales (
  book_id INTEGER,
  copies_sold INTEGER
);

INSERT INTO sales (book_id, copies_sold) VALUES
(1, 10),
(2, NULL),
(3, 5),
(4, NULL),
(5, 15);

WITH non_null_sales AS (
  SELECT * FROM sales WHERE copies_sold IS NOT NULL
)

-- Select the total copies sold using SUM on non_null_sales
-- Your code here

Hint

Use SUM(copies_sold) in the SELECT statement on the filtered rows.

4

Calculate average copies sold ignoring NULLs

Write a SQL query that uses AVG(copies_sold) on the non_null_sales to calculate the average copies sold, ignoring NULL values.

SQL

CREATE TABLE sales (
  book_id INTEGER,
  copies_sold INTEGER
);

INSERT INTO sales (book_id, copies_sold) VALUES
(1, 10),
(2, NULL),
(3, 5),
(4, NULL),
(5, 15);

WITH non_null_sales AS (
  SELECT * FROM sales WHERE copies_sold IS NOT NULL
)

SELECT SUM(copies_sold) AS total_copies_sold FROM non_null_sales;

-- Select the average copies sold using AVG on non_null_sales
-- Your code here

Hint

Use AVG(copies_sold) in the SELECT statement on the filtered 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