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

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

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

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

Need a hint?

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