Conditional aggregation pattern in SQL - Mini Project: Build & Apply

Conditional Aggregation Pattern in SQL

📖 Scenario: You work for a small online store. The store keeps track of orders in a table called orders. Each order has a status which can be 'completed', 'pending', or 'cancelled'. You want to find out how many orders are in each status for each customer.

🎯 Goal: Create an SQL query that uses conditional aggregation to count the number of orders by status for each customer.

📋 What You'll Learn

Create a table called orders with columns order_id (integer), customer_id (integer), and status (text).

Insert exactly these rows into orders: (1, 101, 'completed'), (2, 101, 'pending'), (3, 102, 'completed'), (4, 103, 'cancelled'), (5, 101, 'completed').

Write a query that selects customer_id and counts of orders with status 'completed', 'pending', and 'cancelled' using conditional aggregation.

Use SUM(CASE WHEN ... THEN 1 ELSE 0 END) for counting each status.

💡 Why This Matters

🌍 Real World

Stores and businesses often need to summarize data by categories, like counting orders by status for each customer.

💼 Career

Knowing conditional aggregation is essential for data analysts and database developers to create meaningful reports and insights.

1

Create the orders table and insert data

Write SQL statements to create a table called orders with columns order_id (integer), customer_id (integer), and status (text). Then insert these exact rows: (1, 101, 'completed'), (2, 101, 'pending'), (3, 102, 'completed'), (4, 103, 'cancelled'), (5, 101, 'completed').

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 rows.

2

Add a helper variable for counting

Define a helper variable or expression in your SQL query to count orders. For now, just select customer_id and status from orders to see the data.

SQL

CREATE TABLE orders (
    order_id INTEGER,
    customer_id INTEGER,
    status TEXT
);

INSERT INTO orders (order_id, customer_id, status) VALUES
(1, 101, 'completed'),
(2, 101, 'pending'),
(3, 102, 'completed'),
(4, 103, 'cancelled'),
(5, 101, 'completed');

-- Select customer_id and status from orders
-- Your code here

Hint

Use SELECT customer_id, status FROM orders to view the data before aggregation.

3

Write the conditional aggregation query

Write an SQL query that selects customer_id and counts the number of orders with status 'completed', 'pending', and 'cancelled' using SUM(CASE WHEN status = '...' THEN 1 ELSE 0 END). Group the results by customer_id.

SQL

CREATE TABLE orders (
    order_id INTEGER,
    customer_id INTEGER,
    status TEXT
);

INSERT INTO orders (order_id, customer_id, status) VALUES
(1, 101, 'completed'),
(2, 101, 'pending'),
(3, 102, 'completed'),
(4, 103, 'cancelled'),
(5, 101, 'completed');

-- Write the conditional aggregation query here
-- Your code here

Hint

Use SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) to count completed orders, and similarly for other statuses.

4

Complete the query with ordering

Add an ORDER BY customer_id clause at the end of your query to sort the results by customer_id.

SQL

CREATE TABLE orders (
    order_id INTEGER,
    customer_id INTEGER,
    status TEXT
);

INSERT INTO orders (order_id, customer_id, status) VALUES
(1, 101, 'completed'),
(2, 101, 'pending'),
(3, 102, 'completed'),
(4, 103, 'cancelled'),
(5, 101, 'completed');

SELECT
    customer_id,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_count,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count
FROM orders
GROUP BY customer_id
-- Add ORDER BY customer_id here
-- Your code here

Hint

Use ORDER BY customer_id to sort the results by customer.

Practice

1. What does conditional aggregation in SQL allow you to do?
SUM(CASE WHEN condition THEN value ELSE 0 END)

easy

A. Delete rows that meet a condition

B. Join two tables based on a condition

C. Create a new table from existing data

D. Calculate sums or counts based on specific conditions within one query

Solution

1. Step 1: Understand conditional aggregation concept

   Conditional aggregation uses CASE inside aggregate functions like SUM or COUNT to selectively include values based on conditions.

2. Step 2: Identify what it achieves

   This lets you calculate sums or counts for different conditions in one query without multiple scans.

3. Final Answer:

   Calculate sums or counts based on specific conditions within one query -> Option D

4. Quick Check:

   Conditional aggregation = selective sums/counts [OK]

Hint: Think: aggregate + CASE filters rows inside one query [OK]

Common Mistakes:

• Confusing conditional aggregation with JOIN operations
• Thinking it creates or deletes tables
• Assuming it filters rows outside aggregation

2. Which of the following is the correct syntax using COUNT for counting only rows where status = 'active' using conditional aggregation?

easy

A. COUNT(CASE WHEN status = 'active' THEN 1 ELSE NULL END)

B. COUNT(CASE status = 'active' THEN 1 END)

C. SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END)

D. COUNT(IF status = 'active' THEN 1 ELSE 0 END)

Solution

1. Step 1: Recall correct CASE syntax inside COUNT

   The CASE expression must have WHEN condition THEN value ELSE NULL END for COUNT to count only matching rows.

2. Step 2: Check each option

   COUNT(CASE WHEN status = 'active' THEN 1 ELSE NULL END) uses correct CASE syntax with ELSE NULL, so COUNT counts only 'active' rows. COUNT(CASE status = 'active' THEN 1 END) misses WHEN keyword. SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) uses SUM, not COUNT. COUNT(IF status = 'active' THEN 1 ELSE 0 END) uses invalid IF syntax.

3. Final Answer:

   COUNT(CASE WHEN status = 'active' THEN 1 ELSE NULL END) -> Option A

4. Quick Check:

   COUNT + CASE WHEN ... THEN 1 ELSE NULL END [OK]

Hint: Use ELSE NULL inside CASE for COUNT to skip rows [OK]

Common Mistakes:

• Omitting WHEN keyword in CASE
• Using SUM instead of COUNT for counting rows
• Using ELSE 0 which counts zeros as rows

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 sales amounts greater than 100 per region

B. Count of sales with amount greater than 100 per region

C. Sum of all sales amounts per region

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

Solution

1. Step 1: Analyze the CASE inside SUM

   The CASE returns amount if amount > 100, else 0. So only amounts above 100 contribute to the sum.

2. Step 2: Understand GROUP BY effect

   The query groups rows by region, summing only big sales (amount > 100) per region.

3. Final Answer:

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

4. Quick Check:

   SUM with CASE filters amounts > 100 [OK]

Hint: CASE filters values inside SUM to include only big sales [OK]

Common Mistakes:

• Thinking it sums all sales, ignoring condition
• Confusing SUM with COUNT
• Assuming it sums small sales instead

4. Identify the error in this SQL query using conditional aggregation:

SELECT department, COUNT(CASE WHEN salary > 50000 THEN 1 ELSE 0 END) AS high_earners FROM employees GROUP BY department;

medium

A. SUM should be used instead of COUNT

B. COUNT counts zeros, so it counts all rows, not just high earners

C. CASE syntax is invalid

D. Missing GROUP BY clause

Solution

1. Step 1: Understand COUNT behavior with CASE

   COUNT counts all non-NULL values. Here ELSE 0 returns zero, which is counted as a value, so all rows count.

2. Step 2: Identify correct fix

   To count only high earners, ELSE should be NULL so those rows are ignored by COUNT.

3. Final Answer:

   COUNT counts zeros, so it counts all rows, not just high earners -> Option B

4. Quick Check:

   COUNT counts non-NULL, zero is counted [OK]

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

Common Mistakes:

• Using ELSE 0 causes wrong counts
• Confusing COUNT with SUM
• Ignoring that zero is counted by COUNT

5. You want to find the total sales and the count of sales above 1000 for each product in one query. Which SQL query correctly uses conditional aggregation?

hard

A. SELECT product, COUNT(amount) AS total_sales, SUM(CASE WHEN amount > 1000 THEN 1 ELSE 0 END) AS big_sales_count FROM sales GROUP BY product;

B. SELECT product, SUM(CASE WHEN amount > 1000 THEN amount ELSE 0 END) AS total_sales, COUNT(amount) AS big_sales_count FROM sales GROUP BY product;

C. SELECT product, SUM(amount) AS total_sales, SUM(CASE WHEN amount > 1000 THEN 1 ELSE 0 END) AS big_sales_count FROM sales GROUP BY product;

D. SELECT product, SUM(amount) AS total_sales, COUNT(CASE WHEN amount > 1000 THEN 1 ELSE 0 END) AS big_sales_count FROM sales GROUP BY product;

Solution

1. Step 1: Identify total sales calculation

   Total sales is sum of all amounts, so SUM(amount) is correct.

2. Step 2: Identify count of sales above 1000

   Counting sales above 1000 can be done by summing 1 for each qualifying row and 0 otherwise, so SUM(CASE WHEN amount > 1000 THEN 1 ELSE 0 END) works.

3. Step 3: Check each option

   SELECT product, SUM(amount) AS total_sales, COUNT(CASE WHEN amount > 1000 THEN 1 ELSE 0 END) AS big_sales_count FROM sales GROUP BY product; uses COUNT with ELSE 0 which counts all rows incorrectly; SELECT product, SUM(CASE WHEN amount > 1000 THEN amount ELSE 0 END) AS total_sales, COUNT(amount) AS big_sales_count FROM sales GROUP BY product; incorrectly sums only amounts above 1000 for total_sales; SELECT product, COUNT(amount) AS total_sales, SUM(CASE WHEN amount > 1000 THEN 1 ELSE 0 END) AS big_sales_count FROM sales GROUP BY product; swaps SUM and COUNT incorrectly; SELECT product, SUM(amount) AS total_sales, SUM(CASE WHEN amount > 1000 THEN 1 ELSE 0 END) AS big_sales_count FROM sales GROUP BY product; correctly sums total sales and counts big sales using SUM with CASE.

4. Final Answer:

   SELECT product, SUM(amount) AS total_sales, SUM(CASE WHEN amount > 1000 THEN 1 ELSE 0 END) AS big_sales_count FROM sales GROUP BY product; -> Option C

5. Quick Check:

   SUM for totals, SUM(CASE) for conditional counts [OK]

Hint: Use SUM(CASE) for conditional counts, SUM(column) for totals [OK]

Common Mistakes:

• Using COUNT with ELSE 0 which counts all rows
• Summing only filtered amounts for total sales
• Mixing COUNT and SUM incorrectly