CASE with aggregate functions in SQL - Mini Project: Build & Apply

Using CASE with Aggregate Functions in SQL

📖 Scenario: You work for a retail company that wants to analyze sales data. The company wants to see how many sales were made in different regions and categorize them as 'High' or 'Low' sales based on the total sales amount.

🎯 Goal: Build an SQL query that uses the CASE statement combined with aggregate functions to categorize total sales by region as 'High' or 'Low'.

📋 What You'll Learn

Create a table called sales with columns region (text) and amount (integer).

Insert the exact sales data provided into the sales table.

Write a query that sums sales amounts grouped by region.

Use a CASE statement with the aggregate SUM(amount) to label sales as 'High' if total is 1000 or more, otherwise 'Low'.

💡 Why This Matters

🌍 Real World

Retail companies often analyze sales data by region to make business decisions. Using CASE with aggregate functions helps categorize and summarize data efficiently.

💼 Career

SQL skills like using CASE with aggregates are essential for data analysts and database developers to create meaningful reports and insights.

1

Create the sales table and insert data

Create a table called sales with columns region (text) and amount (integer). Then insert these exact rows into sales: ('North', 500), ('South', 300), ('North', 700), ('East', 200), ('South', 800).

SQL

-- Create the sales table and insert the data
-- Your code here

Need a hint?

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

2

Add a threshold variable for high sales

Create a variable or use a comment to define a threshold value of 1000 to classify sales as 'High' or 'Low'.

SQL

CREATE TABLE sales (region TEXT, amount INTEGER);
INSERT INTO sales (region, amount) VALUES
('North', 500),
('South', 300),
('North', 700),
('East', 200),
('South', 800);
-- Define threshold value for high sales
-- Your code here

Need a hint?

You can add a comment line with the threshold value 1000 to keep it clear.

3

Write the query with CASE and SUM

Write a SELECT query that groups sales by region, sums the amount as total_sales, and uses a CASE statement to create a column sales_category that shows 'High' if total_sales is 1000 or more, otherwise 'Low'.

SQL

CREATE TABLE sales (region TEXT, amount INTEGER);
INSERT INTO sales (region, amount) VALUES
('North', 500),
('South', 300),
('North', 700),
('East', 200),
('South', 800);
-- Threshold for high sales
-- 1000

-- Write the SELECT query with CASE and SUM
-- Your code here

Need a hint?

Use SUM(amount) inside the CASE to compare with 1000.

4

Complete the query with ORDER BY

Add an ORDER BY clause to the query to sort the results by total_sales in descending order.

SQL

CREATE TABLE sales (region TEXT, amount INTEGER);
INSERT INTO sales (region, amount) VALUES
('North', 500),
('South', 300),
('North', 700),
('East', 200),
('South', 800);
-- Threshold for high sales
-- 1000

SELECT region,
       SUM(amount) AS total_sales,
       CASE WHEN SUM(amount) >= 1000 THEN 'High' ELSE 'Low' END AS sales_category
FROM sales
GROUP BY region
-- Add ORDER BY clause here
-- Your code here

Need a hint?

Use ORDER BY total_sales DESC to sort from highest to lowest total sales.