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

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

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

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

Hint

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

Practice

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

easy

A. COUNT(*)

B. COUNT(column_name)

C. SUM(column_name)

D. AVG(column_name)

Solution

1. Step 1: Understand COUNT(*) behavior

   COUNT(*) counts every row in the table regardless of NULLs.

2. Step 2: Compare with other aggregate functions

   COUNT(column_name) counts only non-NULL values; SUM and AVG ignore NULLs but do not count rows.

3. Final Answer:

   COUNT(*) -> Option A

4. Quick Check:

   COUNT(*) counts all rows including NULLs [OK]

Hint: COUNT(*) counts all rows, NULL or not [OK]

Common Mistakes:

• Thinking COUNT(column) counts NULLs
• Assuming SUM counts NULLs as zero
• Confusing AVG with COUNT(*)

2. Which of the following is the correct syntax to count only non-NULL values in the column salary?

easy

A. SELECT COUNT(salary) FROM employees;

B. SELECT COUNT(*) FROM employees;

C. SELECT COUNT(salary) FROM employees WHERE salary IS NULL;

D. SELECT COUNT(*) FROM employees WHERE salary IS NULL;

Solution

1. Step 1: Understand COUNT(column) syntax

   COUNT(column) counts only non-NULL values in that column automatically.

2. Step 2: Analyze other options

   SELECT COUNT(*) FROM employees; counts all rows including those with NULL salary; SELECT COUNT(salary) FROM employees WHERE salary IS NULL; counts non-NULL salaries where salary is NULL (returns 0); SELECT COUNT(*) FROM employees WHERE salary IS NULL; counts rows with NULL salary.

3. Final Answer:

   SELECT COUNT(salary) FROM employees; -> Option A

4. Quick Check:

   COUNT(column) counts non-NULL values [OK]

Hint: Use COUNT(column) to count non-NULL values directly [OK]

Common Mistakes:

• Using WHERE clause unnecessarily with COUNT(column)
• Confusing COUNT(*) with COUNT(column)
• Counting NULL values by mistake

3. Given the table sales with column amount containing values (100, NULL, 200, NULL), what is the result of SELECT SUM(amount) FROM sales;?

medium

A. Syntax Error

B. NULL

C. 0

D. 300

Solution

1. Step 1: Identify values in amount column

   Values are 100, NULL, 200, NULL.

2. Step 2: Understand SUM behavior with NULLs

   SUM ignores NULLs and adds only 100 + 200 = 300.

3. Final Answer:

   300 -> Option D

4. Quick Check:

   SUM ignores NULLs and sums non-NULLs [OK]

Hint: SUM skips NULLs, adds only real numbers [OK]

Common Mistakes:

• Assuming SUM returns NULL if any NULL present
• Confusing SUM with COUNT
• Expecting 0 instead of sum of non-NULLs

4. Consider this query: SELECT AVG(score) FROM tests; where all score values are NULL. What is the issue and how to fix it?

medium

A. AVG returns 0; fix by casting scores to integers

B. AVG returns NULL; fix by filtering NULLs with WHERE score IS NOT NULL

C. Syntax error; fix by adding GROUP BY

D. AVG returns NULL; fix by using COUNT(score) instead

Solution

1. Step 1: Understand AVG behavior with all NULLs

   AVG returns NULL if no non-NULL values exist.

2. Step 2: Fix by filtering NULLs

   Use WHERE score IS NOT NULL to exclude NULLs before averaging.

3. Final Answer:

   AVG returns NULL; fix by filtering NULLs with WHERE score IS NOT NULL -> Option B

4. Quick Check:

   AVG returns NULL if no non-NULL values [OK]

Hint: Filter NULLs before AVG to avoid NULL result [OK]

Common Mistakes:

• Expecting AVG to return 0 with all NULLs
• Trying to fix with GROUP BY unnecessarily
• Using COUNT instead of AVG to get average

5. You want to find the average salary of employees but return 0 if all salaries are NULL. Which query achieves this?

hard

A. SELECT SUM(salary)/COUNT(*) FROM employees;

B. SELECT AVG(COALESCE(salary, 0)) FROM employees;

C. SELECT COALESCE(AVG(salary), 0) FROM employees;

D. SELECT COUNT(salary) FROM employees WHERE salary IS NOT NULL;

Solution

1. Step 1: Understand AVG with all NULL salaries

   AVG returns NULL if no non-NULL salaries exist.

2. Step 2: Use COALESCE to replace NULL with 0

   COALESCE(AVG(salary), 0) returns 0 instead of NULL when AVG is NULL.

3. Step 3: Analyze other options

   SELECT AVG(COALESCE(salary, 0)) FROM employees; averages zeros which skews result; SELECT SUM(salary)/COUNT(*) FROM employees; divides by total rows including NULLs; SELECT COUNT(salary) FROM employees WHERE salary IS NOT NULL; counts non-NULL salaries only.

4. Final Answer:

   SELECT COALESCE(AVG(salary), 0) FROM employees; -> Option C

5. Quick Check:

   Use COALESCE to handle NULL AVG results [OK]

Hint: Wrap AVG with COALESCE to replace NULL with zero [OK]

Common Mistakes:

• Replacing NULL salaries with zero before AVG skews average
• Dividing SUM by total rows including NULLs
• Using COUNT instead of AVG for average