NULL behavior in aggregate functions in SQL - Mini Project: Build & Apply

Understanding NULL Behavior in Aggregate Functions

📖 Scenario: You are working with a sales database that tracks orders and their amounts. Some orders have missing amounts recorded as NULL. You want to learn how SQL aggregate functions handle these NULL values.

🎯 Goal: Build SQL queries step-by-step to see how aggregate functions like COUNT, SUM, and AVG behave when the data contains NULL values.

📋 What You'll Learn

Create a table called orders with columns order_id and amount.

Insert specific rows including NULL values into the orders table.

Write a query to count all rows including NULLs using COUNT(*).

Write a query to count only non-NULL amount values using COUNT(amount).

Write a query to calculate the sum of amount values ignoring NULLs using SUM(amount).

Write a query to calculate the average of amount values ignoring NULLs using AVG(amount).

💡 Why This Matters

🌍 Real World

Handling NULL values correctly is important in real databases to get accurate reports and summaries.

💼 Career

Understanding NULL behavior in aggregate functions is essential for data analysts, database developers, and anyone working with SQL queries.

1

Create the orders table and insert data

Create a table called orders with columns order_id as integer and amount as integer. Then insert these rows exactly: (1, 100), (2, NULL), (3, 200), (4, NULL), (5, 300).

SQL

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

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows with NULL values.

2

Count all rows including NULLs with COUNT(*)

Write a SQL query that selects the total number of rows in the orders table using COUNT(*) and name the result column total_orders.

SQL

CREATE TABLE orders (
    order_id INT,
    amount INT
);

INSERT INTO orders (order_id, amount) VALUES
(1, 100),
(2, NULL),
(3, 200),
(4, NULL),
(5, 300);

-- Write a query to count all rows using COUNT(*)
-- Your code here

Need a hint?

Use COUNT(*) to count all rows regardless of NULL values.

3

Count only non-NULL amount values with COUNT(amount)

Write a SQL query that counts only the non-NULL amount values in the orders table using COUNT(amount) and name the result column non_null_amounts.

SQL

CREATE TABLE orders (
    order_id INT,
    amount INT
);

INSERT INTO orders (order_id, amount) VALUES
(1, 100),
(2, NULL),
(3, 200),
(4, NULL),
(5, 300);

SELECT COUNT(*) AS total_orders FROM orders;

-- Write a query to count non-NULL amounts using COUNT(amount)
-- Your code here

Need a hint?

Use COUNT(amount) to count only rows where amount is not NULL.

4

Calculate SUM(amount) and AVG(amount) ignoring NULLs

Write a SQL query that selects the sum of amount as total_amount and the average of amount as average_amount from the orders table using SUM(amount) and AVG(amount).

SQL

CREATE TABLE orders (
    order_id INT,
    amount INT
);

INSERT INTO orders (order_id, amount) VALUES
(1, 100),
(2, NULL),
(3, 200),
(4, NULL),
(5, 300);

SELECT COUNT(*) AS total_orders FROM orders;

SELECT COUNT(amount) AS non_null_amounts FROM orders;

-- Write a query to select SUM(amount) as total_amount and AVG(amount) as average_amount
-- Your code here

Need a hint?

Use SUM(amount) and AVG(amount) to calculate totals and averages ignoring NULL values.