GROUP BY with NULL values behavior in SQL - Mini Project: Build & Apply

Understanding GROUP BY with NULL Values in SQL

📖 Scenario: You work in a small company database team. You have a table that records sales transactions. Some transactions have a known region, but some have NULL because the region was not recorded.You want to learn how SQL groups rows when NULL values appear in the GROUP BY column.

🎯 Goal: Create a table called sales with columns id, region, and amount. Insert some rows including NULL in region. Then write a query that groups sales by region and sums the amount. Observe how NULL values are handled in grouping.

📋 What You'll Learn

Create a table named sales with columns id (integer), region (text, nullable), and amount (integer).

Insert exactly these rows into sales: (1, 'North', 100), (2, 'South', 150), (3, NULL, 200), (4, 'North', 50), (5, NULL, 300).

Write a SELECT query that groups by region and calculates the total amount per region.

Use the exact column names region and total_amount in the query output.

💡 Why This Matters

🌍 Real World

Grouping data by categories often includes missing or unknown values represented as NULL. Understanding how SQL treats NULL in GROUP BY helps in accurate reporting and analysis.

💼 Career

Database developers and analysts frequently write GROUP BY queries. Knowing how NULL values behave ensures correct aggregation and prevents mistakes in business reports.

1

Create the sales table

Write a SQL statement to create a table called sales with columns: id as integer primary key, region as text that can be NULL, and amount as integer.

SQL

-- Create the sales table with id, region, and amount columns
-- Your code here

Hint

Use CREATE TABLE sales and define id as primary key integer, region as text nullable, and amount as integer.

2

Insert sales data including NULL regions

Write SQL INSERT statements to add these rows into sales: (1, 'North', 100), (2, 'South', 150), (3, NULL, 200), (4, 'North', 50), (5, NULL, 300).

SQL

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    region TEXT,
    amount INTEGER
);
-- Insert the 5 sales rows here
-- Your code here

Hint

Use a single INSERT INTO sales (id, region, amount) VALUES statement with all 5 rows, including NULL for region where needed.

3

Write a GROUP BY query to sum amounts by region

Write a SQL SELECT query that groups rows by region and calculates the sum of amount as total_amount. Use GROUP BY region.

SQL

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    region TEXT,
    amount INTEGER
);

INSERT INTO sales (id, region, amount) VALUES
(1, 'North', 100),
(2, 'South', 150),
(3, NULL, 200),
(4, 'North', 50),
(5, NULL, 300);

-- Write the GROUP BY query here
-- Your code here

Hint

Use SELECT region, SUM(amount) AS total_amount FROM sales GROUP BY region to group and sum.

4

Complete the query with ORDER BY to see NULL group last

Add an ORDER BY clause to the previous query to sort results by region ascending, so that the NULL group appears last.

SQL

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    region TEXT,
    amount INTEGER
);

INSERT INTO sales (id, region, amount) VALUES
(1, 'North', 100),
(2, 'South', 150),
(3, NULL, 200),
(4, 'North', 50),
(5, NULL, 300);

SELECT region, SUM(amount) AS total_amount
FROM sales
GROUP BY region
-- Add ORDER BY to sort region ascending with NULL last
-- Your code here

Hint

Use ORDER BY region IS NULL, region ASC to put NULLs last and sort others alphabetically.

Practice

1. What happens to NULL values when you use GROUP BY on a column containing them?

easy

A. All NULL values are grouped together as one group.

B. NULL values are ignored and not included in any group.

C. Each NULL value forms its own separate group.

D. GROUP BY causes an error if NULL values exist.

Solution

1. Step 1: Understand how GROUP BY handles NULLs

   In SQL, GROUP BY treats all NULL values in a column as equal, grouping them into one group.

2. Step 2: Confirm behavior with example

   If a column has multiple rows with NULL, they appear as a single group in the result.

3. Final Answer:

   All NULL values are grouped together as one group. -> Option A

4. Quick Check:

   GROUP BY NULL = one group [OK]

Hint: Remember: NULLs group together, not separately [OK]

Common Mistakes:

• Thinking NULLs are ignored in GROUP BY
• Assuming each NULL is a separate group
• Believing GROUP BY errors on NULL values

2. Which of the following SQL queries correctly groups rows by a column that may contain NULL values?

easy

A. SELECT category, COUNT(*) FROM products GROUP BY category;

B. SELECT category, COUNT(*) FROM products GROUP BY category WHERE category IS NOT NULL;

C. SELECT category, COUNT(*) FROM products WHERE category IS NOT NULL GROUP BY category;

D. SELECT category, COUNT(*) FROM products GROUP BY category HAVING category IS NOT NULL;

Solution

1. Step 1: Check GROUP BY syntax with NULLs

   SELECT category, COUNT(*) FROM products GROUP BY category; uses correct syntax: grouping by category including NULLs. GROUP BY works with NULL values without extra filters.

2. Step 2: Analyze other options

   Options A and D misuse WHERE and HAVING clauses with GROUP BY. SELECT category, COUNT(*) FROM products WHERE category IS NOT NULL GROUP BY category; filters out NULLs before grouping, which is valid but excludes NULL groups.

3. Final Answer:

   SELECT category, COUNT(*) FROM products GROUP BY category; -> Option A

4. Quick Check:

   GROUP BY with NULLs needs no special filter [OK]

Hint: GROUP BY works directly with NULLs, no WHERE needed [OK]

Common Mistakes:

• Using WHERE after GROUP BY (syntax error)
• Filtering NULLs before grouping unintentionally
• Misusing HAVING clause for filtering NULLs

3. Given the table sales with data:

product | region
-------|--------
A      | East
B      | NULL
A      | NULL
B      | East
NULL   | West
NULL   | NULL

What is the result of:

SELECT region, COUNT(*) FROM sales GROUP BY region ORDER BY region;

medium

A. [
{"region": "East", "count": 2},
{"region": "NULL", "count": 3},
{"region": "West", "count": 1}
]

B. [
{"region": "East", "count": 2},
{"region": "NULL", "count": 2},
{"region": "West", "count": 1}
]

C. [
{"region": "East", "count": 2},
{"region": null, "count": 2},
{"region": "West", "count": 1}
]

D. [
{"region": null, "count": 3},
{"region": "East", "count": 2},
{"region": "West", "count": 1}
]

Solution

1. Step 1: Group rows by region including NULLs

   Rows with region 'East' = 2, 'West' = 1, and NULL values (3 rows) are grouped together as one NULL group.

2. Step 2: Understand NULL display and count

   SQL returns NULL as null (not string 'NULL'). Count for NULL group is 3 because three rows have region NULL. ORDER BY region ASC places null first.

3. Final Answer:

   [{"region": null, "count": 3}, {"region": "East", "count": 2}, {"region": "West", "count": 1}] -> Option D

4. Quick Check:

   GROUP BY NULL groups count 3, NULL shown as null [OK]

Hint: NULLs group together and show as null, not 'NULL' string [OK]

Common Mistakes:

• Counting NULL rows separately
• Displaying NULL as string 'NULL'
• Miscounting NULL group size

4. Consider this query:

SELECT department, COUNT(*) FROM employees GROUP BY department;

It returns an error. Which fix will correctly handle NULL values in department to avoid errors?

medium

A. Add WHERE department IS NOT NULL before GROUP BY.

B. No fix needed; GROUP BY never errors on NULL.

C. Use HAVING department IS NOT NULL after GROUP BY.

D. Replace NULL with a string using COALESCE(department, 'Unknown') in SELECT and GROUP BY.

Solution

1. Step 1: Understand why no error occurs

   In standard SQL, GROUP BY handles NULL values correctly by grouping all NULLs together into one group. No error is thrown.

2. Step 2: Confirm no fix needed

   The query runs successfully and includes a NULL group in the results.

3. Final Answer:

   No fix needed; GROUP BY never errors on NULL. -> Option B

4. Quick Check:

   GROUP BY NULL = no error [OK]

Hint: GROUP BY handles NULLs without error [OK]

Common Mistakes:

• Thinking GROUP BY errors on NULLs
• Unnecessarily filtering out NULLs with WHERE
• Misusing HAVING for pre-group filtering

5. You have a table orders with columns customer_id and status, where status can be NULL. You want to count orders by status, treating all NULL statuses as 'Pending'. Which query correctly achieves this?

hard

A. SELECT status, COUNT(*) FROM orders GROUP BY status WHERE status IS NULL;

B. SELECT status, COUNT(*) FROM orders GROUP BY status HAVING status IS NOT NULL;

C. SELECT COALESCE(status, 'Pending') AS order_status, COUNT(*) FROM orders GROUP BY order_status;

D. SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY status;

Solution

1. Step 1: Replace NULL with 'Pending' using COALESCE

   COALESCE(status, 'Pending') converts NULL statuses to 'Pending' for counting.

2. Step 2: Group by the alias used in SELECT

   Grouping by order_status ensures all NULLs are counted under 'Pending'.

3. Final Answer:

   SELECT COALESCE(status, 'Pending') AS order_status, COUNT(*) FROM orders GROUP BY order_status; -> Option C

4. Quick Check:

   COALESCE + GROUP BY alias counts NULL as 'Pending' [OK]

Hint: Use COALESCE and group by alias to count NULLs as desired [OK]

Common Mistakes:

• Filtering out NULLs instead of replacing them
• Using WHERE after GROUP BY (syntax error)
• Grouping by original column without COALESCE