Advanced SQL Reporting: CASE & Conditional Aggregation

1. The CASE Statement: SQL's Conditional Logic

At its core, the CASE statement allows you to apply if/then/else logic directly within your SELECT clause. It evaluates a series of conditions and returns a specific value as soon as a condition is met.

The basic structure is:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE else_result
END

This is extremely useful for segmenting data into custom groups. For example, you could categorize customers into 'New', 'Regular', or 'VIP' based on their purchase history, or label products as 'High-Margin' or 'Low-Margin'.

To get a clear visual understanding of how the CASE statement works, please watch the following video.

As the video showed, the CASE statement is evaluated for each row, and the order of the WHEN clauses matters. SQL will stop at the first WHEN clause that evaluates to true. The optional ELSE clause acts as a default, catching any rows that don't meet any of the preceding conditions. If ELSE is omitted and no conditions are met, the result will be NULL.

For a quick written reference on the syntax, this article is a great resource.

2. Conditional Aggregation: Unleashing the Power of CASE

While creating categories is useful, the CASE statement truly shines when you place it inside an aggregate function like SUM(), COUNT(), or AVG(). This is called conditional aggregation.

This technique allows you to aggregate different subsets of your data in parallel, within a single GROUP BY query. Instead of writing multiple queries with different WHERE clauses and joining them, you can compute multiple metrics in one pass.

The most common pattern looks like this:

SUM(CASE WHEN condition THEN column_to_sum ELSE 0 END)

Let's break it down:

• The CASE statement checks the condition for each row.
• If the condition is true, it returns the value from column_to_sum.
• If the condition is false, it returns 0.
• The SUM() function then adds up these results. The effect is that you are only summing the values for rows that meet your condition.

Let's see how to apply this to create targeted reports.

This method of "flagging" rows with a 1 or 0 inside a CASE statement is fundamental for analytics. By summing the flags, you get a conditional COUNT. By summing a value (like revenue) conditionally, you get a conditional SUM.

The following article explains why this is such an important pattern in data science and analytics interviews.

3. Pivoting Data: From Rows to Columns

One of the most powerful applications of conditional aggregation is pivoting data. This means transforming data from a "long" format (where attributes are stored in rows) to a "wide" format (where attributes are stored in columns). This is incredibly useful for creating summary dashboards and reports that are easy to read.

Imagine you have a table of sales data like this:

You want to create a report with one row per region and separate columns for Electronics sales and Clothing sales.

Desired Output:

You can achieve this with conditional aggregation:

SELECT
  region,
  SUM(CASE WHEN category = 'Electronics' THEN sales ELSE 0 END) AS electronics_sales,
  SUM(CASE WHEN category = 'Clothing' THEN sales ELSE 0 END) AS clothing_sales
FROM sales_data
GROUP BY region;

This video provides a clear, step-by-step walkthrough of this exact process.

This SUM(CASE WHEN ...) pattern is the standard, cross-database method for pivoting data and is an essential skill for any data professional.

4. Practice Exercises

Let's put these concepts into practice. Consider the following transactions table:

Problem 1: Categorize Transaction Sizes

Task: Write a query that returns the transaction_id, amount, and a new column transaction_size. The size should be 'Small' for amounts under 100, 'Medium' for amounts between 100 and 250 (inclusive), and 'Large' for amounts over 250.

SELECT
  transaction_id,
  amount,
  CASE
    WHEN amount < 100 THEN 'Small'
    WHEN amount >= 100 AND amount <= 250 THEN 'Medium'
    ELSE 'Large'
  END AS transaction_size
FROM transactions;

Problem 2: Create a Customer Spending Report

Task: Write a query that pivots the data to show the total amount each customer spent in each product category. The final report should have one row per customer_id.

Desired Output:

SELECT
  customer_id,
  SUM(CASE WHEN product_category = 'Groceries' THEN amount ELSE 0 END) AS groceries_total,
  SUM(CASE WHEN product_category = 'Electronics' THEN amount ELSE 0 END) AS electronics_total,
  SUM(CASE WHEN product_category = 'Clothing' THEN amount ELSE 0 END) AS clothing_total
FROM transactions
GROUP BY customer_id
ORDER BY customer_id;

Conclusion

In this lesson, you've learned how to harness conditional logic in SQL using the CASE statement. While useful for basic categorization, its true power is unlocked through conditional aggregation, allowing you to build complex, pivoted reports efficiently.

Key Takeaways:

• The CASE statement provides if/then/else functionality directly in your queries.
• You can use CASE to create new columns with categorical or derived values.
• Conditional aggregation is the technique of placing a CASE statement inside an aggregate function (SUM, COUNT, AVG).
• The SUM(CASE WHEN ...) pattern is the standard way to pivot data, transforming it from a long to a wide format for creating summary reports.

In our next lesson, we will start applying these advanced SQL techniques—including CASE, window functions, and JOINs—to solve realistic business problems. We'll begin by tackling a common data science challenge: performing a user retention analysis.