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

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

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

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

Hint

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

Practice

1. What does the following SQL snippet do?
SELECT SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) FROM users;

easy

A. Counts how many users have status other than 'active'.

B. Calculates the total number of users regardless of status.

C. Returns the sum of all status values.

D. Counts how many users have status 'active'.

Solution

1. Step 1: Understand the CASE inside SUM

   The CASE returns 1 for each row where status is 'active', else 0.

2. Step 2: SUM adds all 1s and 0s

   SUM counts how many rows have status 'active' by adding 1s.

3. Final Answer:

   Counts how many users have status 'active'. -> Option D

4. Quick Check:

   SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts matches [OK]

Hint: SUM with CASE counting 1s counts matching rows [OK]

Common Mistakes:

• Thinking SUM adds status values instead of counting
• Confusing ELSE 0 with ELSE NULL
• Assuming it counts non-active users

2. Which of the following is the correct syntax to count rows where score is greater than 50 using CASE inside COUNT?

easy

A. SELECT COUNT(CASE WHEN score > 50 THEN 1 END) FROM results;

B. SELECT COUNT(CASE score > 50 THEN 1 ELSE 0 END) FROM results;

C. SELECT COUNT(CASE WHEN score > 50 THEN 1 ELSE 0 END) FROM results;

D. SELECT COUNT(CASE score > 50 THEN 1 END) FROM results;

Solution

1. Step 1: Recall COUNT counts non-null values

   COUNT counts rows where expression is NOT NULL.

2. Step 2: Use CASE to return 1 for matches, NULL otherwise

   CASE WHEN condition THEN 1 END returns 1 or NULL (default ELSE NULL).

3. Final Answer:

   SELECT COUNT(CASE WHEN score > 50 THEN 1 END) FROM results; -> Option A

4. Quick Check:

   COUNT with CASE returning NULL for no match counts only matches [OK]

Hint: COUNT counts non-null, so ELSE NULL is default [OK]

Common Mistakes:

• Using ELSE 0 causes COUNT to count all rows
• Omitting WHEN keyword
• Using invalid CASE syntax

3. Given the table sales with columns region and amount, what is the output of this query?

SELECT region, SUM(CASE WHEN amount > 100 THEN amount ELSE 0 END) AS big_sales FROM sales GROUP BY region;

medium

A. Sum of all sales amounts per region.

B. Sum of sales amounts greater than 100 per region.

C. Count of sales greater than 100 per region.

D. Sum of sales amounts less than or equal to 100 per region.

Solution

1. Step 1: Analyze CASE inside SUM

   CASE returns amount if > 100, else 0, so only big sales count fully.

2. Step 2: SUM adds these amounts per region

   SUM totals amounts > 100 per region, ignoring smaller sales by adding 0.

3. Final Answer:

   Sum of sales amounts greater than 100 per region. -> Option B

4. Quick Check:

   SUM with CASE filters values conditionally [OK]

Hint: SUM with CASE ELSE 0 sums only matching values [OK]

Common Mistakes:

• Thinking it counts rows instead of sums amounts
• Ignoring ELSE 0 and expecting NULL filtering
• Confusing greater than with less than

4. Identify the error in this query:

SELECT department, COUNT(CASE department = 'HR' THEN 1 ELSE NULL END) FROM employees GROUP BY department;

medium

A. Missing WHEN keyword in CASE statement.

B. COUNT cannot be used with CASE.

C. GROUP BY should include COUNT column.

D. ELSE NULL is invalid in CASE.

Solution

1. Step 1: Check CASE syntax

   CASE requires WHEN before condition; here it's missing.

2. Step 2: Confirm correct syntax

   Correct form: CASE WHEN department = 'HR' THEN 1 ELSE NULL END.

3. Final Answer:

   Missing WHEN keyword in CASE statement. -> Option A

4. Quick Check:

   CASE syntax needs WHEN before condition [OK]

Hint: CASE must have WHEN before condition [OK]

Common Mistakes:

• Omitting WHEN keyword
• Misusing COUNT with CASE
• Incorrect GROUP BY usage

5. You want to find the average salary of employees, but only include salaries above 50000. Which query correctly uses CASE with AVG to achieve this?

hard

A. SELECT AVG(CASE WHEN salary > 50000 THEN salary ELSE 0 END) FROM employees;

B. SELECT AVG(CASE salary > 50000 THEN salary END) FROM employees;

C. SELECT AVG(CASE WHEN salary > 50000 THEN salary ELSE NULL END) FROM employees;

D. SELECT AVG(CASE salary > 50000 THEN salary ELSE NULL END) FROM employees;

Solution

1. Step 1: Understand AVG ignores NULLs

   AVG calculates average of non-null values only.

2. Step 2: Use CASE to return salary if > 50000, else NULL

   This excludes salaries ≤ 50000 from average calculation.

3. Final Answer:

   SELECT AVG(CASE WHEN salary > 50000 THEN salary ELSE NULL END) FROM employees; -> Option C

4. Quick Check:

   AVG with CASE ELSE NULL excludes unwanted rows [OK]

Hint: Use ELSE NULL to exclude rows from AVG calculation [OK]

Common Mistakes:

• Using ELSE 0 includes zeros in average, skewing result
• Omitting WHEN keyword
• Using invalid CASE syntax