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

Need a 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

Need a 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

Need a 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

Need a hint?

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