Case Statement in SQL: Complete Guide to CASE WHEN & Conditional Aggregation

Stakeholders frequently ask questions requiring side-by-side metric comparisons:

• "Show me completed revenue and refunded revenue side by side for every payment method."
• "What percentage of orders in each category were discounted?"
• "Bucket our transactions into Small, Medium, and Large tiers and count how many orders fall in each."

Without conditional aggregation, analysts often attempt messy self-joins or multiple subqueries joined together, forcing the database engine to scan the same table 3 or 4 times.

With CASE WHEN and conditional aggregation, you solve these problems in a single, elegant query pass.

In this guide, we master CASE WHEN fundamentals, learn how to categorize continuous distributions into discrete business buckets, and implement SUM(CASE WHEN...) and COUNT(CASE WHEN...) pivoting patterns using verified numbers from a live PostgreSQL e-commerce dataset with 2,000 orders. For complementary query optimization guides, explore our SQL GROUP BY vs HAVING Guide and SQL CTE Guide.

1. Case Statement in SQL: Syntax and Fundamentals

The case statement in sql (using the CASE WHEN expression) is SQL's built-in if / else-if / else control structure. It evaluates a sequence of conditions top to bottom and returns the value associated with the first condition that evaluates to TRUE.

Syntax

CASE 
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ELSE fallback_result
END

Categorizing Order Values into Size Bands

Let's inspect individual orders and tag each with a descriptive size_band:

SELECT order_id,
       total_amount,
       CASE 
           WHEN total_amount < 0   THEN 'refund'
           WHEN total_amount < 100 THEN 'small'
           WHEN total_amount < 500 THEN 'medium'
           ELSE 'large'
       END AS size_band
FROM orders
ORDER BY order_id
LIMIT 10;

The "First Match Wins" Evaluation Rule

Because CASE evaluates sequentially:

1. An order with total_amount = -150.00 satisfies total_amount < 0 and is immediately tagged 'refund'.
2. An order with total_amount = 45.00 skips the first check, satisfies total_amount < 100, and becomes 'small'.
3. An order with total_amount = 650.00 skips all WHEN conditions and defaults to 'large' via ELSE.

2. Bucketing Continuous Data with GROUP BY

A CASE expression produces a standard value, meaning you can group by it directly to transform continuous numerical columns into categorical distributions:

SELECT CASE 
           WHEN total_amount < 0   THEN 'refund'
           WHEN total_amount < 100 THEN 'small'
           WHEN total_amount < 500 THEN 'medium'
           ELSE 'large'
       END AS size_band,
       COUNT(*) AS total_orders,
       ROUND(SUM(total_amount), 2) AS total_band_revenue
FROM orders
GROUP BY size_band
ORDER BY total_orders DESC;

The Distribution Breakdown

In a single statement, 2,000 raw transaction amounts are organized into clear business tiers. Notice that 879 orders belong to the large tier (≥₹500), while 195 negative refund records are isolated rather than distorting standard purchase tiers.

3. Conditional Aggregation: Pivoting Rows into Columns

The true superpower of CASE WHEN emerges when placed inside aggregate functions.

The Business Question

"Show me completed revenue and refunded revenue side by side for every payment method."

The Slow, Inefficient Way (Multiple Joins)

A naive approach joins the orders table to itself multiple times:

-- ❌ SLOW & REPETITIVE: Multiple full-table scans
SELECT c.payment_method,
       c.completed_rev,
       r.refunded_rev
FROM (
    SELECT payment_method, SUM(total_amount) AS completed_rev
    FROM orders WHERE status = 'completed' GROUP BY payment_method
) AS c
JOIN (
    SELECT payment_method, SUM(total_amount) AS refunded_rev
    FROM orders WHERE status = 'refunded' GROUP BY payment_method
) AS r ON c.payment_method = r.payment_method;

The Optimal Way: Conditional Aggregation (Single Table Scan)

-- ✅ CLEAN & FAST: Single-pass conditional aggregation
SELECT payment_method,
       ROUND(SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END), 2) AS completed_rev,
       ROUND(SUM(CASE WHEN status = 'refunded'  THEN total_amount ELSE 0 END), 2) AS refunded_rev,
       ROUND(SUM(total_amount), 2) AS net_revenue
FROM orders
GROUP BY payment_method
ORDER BY payment_method;

The Live Output

How It Works Under the Hood

1. For every row in orders, the engine checks status = 'completed'.
2. If true, it returns total_amount; if false, it returns 0.
3. SUM() adds those numbers together.
4. The exact same process occurs concurrently for the refunded column.
5. In a single scan across all 2,000 rows, the table is pivoted into a side-by-side financial report.

4. The COUNT vs SUM Trap with CASE WHEN

A classic technical interview question tests candidates on counting rows conditionally.

The Broken Pattern: COUNT with ELSE 0

-- ❌ THIS CODE PRODUCES WRONG COUNTS
SELECT payment_method,
       COUNT(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_count
FROM orders
GROUP BY payment_method;

Why it fails: COUNT(expression) counts every row where the expression is NOT NULL. Because 0 is a non-null value, COUNT treats 0 as a valid row. The query returns the total count of all orders, not just completed ones!

The Two Correct Patterns

-- ✅ PATTERN 1: SUM with 1 and 0 (Most common)
SELECT payment_method,
       SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_count
FROM orders
GROUP BY payment_method;
 
-- ✅ PATTERN 2: COUNT with NULL fallback (Omit ELSE)
SELECT payment_method,
       COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_count
FROM orders
GROUP BY payment_method;

In Pattern 2, when status <> 'completed', the omitted ELSE returns NULL. Since COUNT ignores NULL, it accurately counts only completed rows.

5. Computing Ratios and Percentages

Conditional aggregation makes calculating conversion rates, refund rates, and fulfillment ratios simple.

Calculating Payment Method Refund Rates

SELECT payment_method,
       COUNT(*) AS total_orders,
       SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refund_orders,
       ROUND(
           (SUM(CASE WHEN status = 'refunded' THEN 1.0 ELSE 0 END) / COUNT(*)) * 100,
           2
       ) AS refund_rate_pct
FROM orders
GROUP BY payment_method
ORDER BY refund_rate_pct DESC;

6. PostgreSQL Alternative: The FILTER (WHERE ...) Clause

Modern PostgreSQL (and SQLite) supports the ANSI SQL standard FILTER clause, providing a cleaner syntax for conditional aggregation:

SELECT payment_method,
       ROUND(SUM(total_amount) FILTER (WHERE status = 'completed'), 2) AS completed_rev,
       ROUND(SUM(total_amount) FILTER (WHERE status = 'refunded'), 2)  AS refunded_rev,
       COUNT(*) FILTER (WHERE status = 'completed')                    AS completed_count
FROM orders
GROUP BY payment_method
ORDER BY payment_method;

FILTER (WHERE ...) is semantically identical to SUM(CASE WHEN ... END) but avoids verbose CASE/WHEN/THEN/ELSE/END boilerplates.

7. Feature Comparison Matrix

8. Summary & Practice

• Use CASE WHEN to classify raw continuous variables into actionable business bands.
• Master SUM(CASE WHEN ...) to pivot row data into side-by-side metric columns without slow self-joins.
• Never use COUNT with ELSE 0—use SUM(... THEN 1 ELSE 0) or COUNT(... THEN 1 END) to count conditionally.
• Check evaluation order—remember that the first TRUE condition wins and subsequent branches are ignored.

Practice conditional aggregation drills, metric pivots, and ratio calculations in the Topfolio Interactive SQL Sandbox or prepare for data analytics interviews with our 30 SQL Interview Questions. For complete query references and execution diagnostics, review our SQL Cheat Sheet, master subqueries in SQL, and understand the order of execution in SQL. To master advanced query framing and subquery refactoring, check out our SQL Subqueries vs CTEs Guide and SQL GROUP BY vs HAVING Guide.