Lesson 7: CASE Expressions

When querying data, you sometimes want to display different values based on conditions, like "if the price is 1 million won or more, label it 'Premium'; otherwise, 'Standard'." CASE expressions let you do conditional branching within SQL.

CASE is SQL's conditional expression, similar to if/else in programming languages. It can handle value conversion, label creation, data bucketing, and conditional aggregation all within a single query.

CASE is SQL's if-else. It evaluates conditions from top to bottom in order.

Simple CASE

A simple CASE compares a single column value against fixed values.

-- Convert order status codes to readable labels
SELECT
    order_number,
    total_amount,
    CASE status
        WHEN 'pending'          THEN 'Awaiting Payment'
        WHEN 'paid'             THEN 'Payment Complete'
        WHEN 'preparing'        THEN 'Preparing'
        WHEN 'shipped'          THEN 'Shipped'
        WHEN 'delivered'        THEN 'Delivered'
        WHEN 'confirmed'        THEN 'Purchase Confirmed'
        WHEN 'cancelled'        THEN 'Cancelled'
        WHEN 'return_requested' THEN 'Return Requested'
        WHEN 'returned'         THEN 'Returned'
        ELSE status
    END AS status_label
FROM orders
ORDER BY ordered_at DESC
LIMIT 5;

Result:

Searched CASE

A searched CASE evaluates independent WHEN conditions, providing full flexibility for comparisons and expressions.

-- Classify products by price tier
SELECT
    name,
    price,
    CASE
        WHEN price < 50           THEN 'Budget'
        WHEN price BETWEEN 50 AND 199.99  THEN 'Mid-range'
        WHEN price BETWEEN 200 AND 799.99 THEN 'High-end'
        ELSE 'Premium'
    END AS price_tier
FROM products
WHERE is_active = 1
ORDER BY price ASC
LIMIT 10;

Result:

Using CASE for Age Group Classification

-- Classify customers by generation
SELECT
    name,
    birth_date,
    CASE
        WHEN birth_date IS NULL THEN 'Unknown'
        WHEN CAST(SUBSTR(birth_date, 1, 4) AS INTEGER) >= 1997 THEN 'Gen Z'
        WHEN CAST(SUBSTR(birth_date, 1, 4) AS INTEGER) >= 1981 THEN 'Millennial'
        WHEN CAST(SUBSTR(birth_date, 1, 4) AS INTEGER) >= 1965 THEN 'Gen X'
        ELSE 'Baby Boomer+'
    END AS generation
FROM customers
LIMIT 8;

SELECT
    name,
    birth_date,
    CASE
        WHEN birth_date IS NULL THEN 'Unknown'
        WHEN YEAR(birth_date) >= 1997 THEN 'Gen Z'
        WHEN YEAR(birth_date) >= 1981 THEN 'Millennial'
        WHEN YEAR(birth_date) >= 1965 THEN 'Gen X'
        ELSE 'Baby Boomer+'
    END AS generation
FROM customers
LIMIT 8;

SELECT
    name,
    birth_date,
    CASE
        WHEN birth_date IS NULL THEN 'Unknown'
        WHEN EXTRACT(YEAR FROM birth_date) >= 1997 THEN 'Gen Z'
        WHEN EXTRACT(YEAR FROM birth_date) >= 1981 THEN 'Millennial'
        WHEN EXTRACT(YEAR FROM birth_date) >= 1965 THEN 'Gen X'
        ELSE 'Baby Boomer+'
    END AS generation
FROM customers
LIMIT 8;

Result:

CASE in GROUP BY and Aggregation

CASE can be used as a grouping expression or within aggregate functions.

-- Product count by price tier
SELECT
    CASE
        WHEN price < 50           THEN 'Budget (under 50K)'
        WHEN price BETWEEN 50 AND 199.99  THEN 'Mid-range (50K-200K)'
        WHEN price BETWEEN 200 AND 799.99 THEN 'High-end (200K-800K)'
        ELSE 'Premium (800K+)'
    END AS price_tier,
    COUNT(*)   AS product_count,
    AVG(price) AS avg_price
FROM products
WHERE is_active = 1
GROUP BY price_tier
ORDER BY avg_price;

Result:

-- Pivot: display order status counts as columns
SELECT
    SUBSTR(ordered_at, 1, 7) AS year_month,
    COUNT(CASE WHEN status = 'confirmed' THEN 1 END) AS confirmed,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled,
    COUNT(CASE WHEN status = 'returned'  THEN 1 END) AS returned,
    COUNT(*) AS total
FROM orders
WHERE ordered_at LIKE '2024%'
GROUP BY SUBSTR(ordered_at, 1, 7)
ORDER BY year_month;

SELECT
    DATE_FORMAT(ordered_at, '%Y-%m') AS year_month,
    COUNT(CASE WHEN status = 'confirmed' THEN 1 END) AS confirmed,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled,
    COUNT(CASE WHEN status = 'returned'  THEN 1 END) AS returned,
    COUNT(*) AS total
FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2025-01-01'
GROUP BY DATE_FORMAT(ordered_at, '%Y-%m')
ORDER BY year_month;

SELECT
    TO_CHAR(ordered_at, 'YYYY-MM') AS year_month,
    COUNT(CASE WHEN status = 'confirmed' THEN 1 END) AS confirmed,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled,
    COUNT(CASE WHEN status = 'returned'  THEN 1 END) AS returned,
    COUNT(*) AS total
FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2025-01-01'
GROUP BY TO_CHAR(ordered_at, 'YYYY-MM')
ORDER BY year_month;

Result:

CASE in ORDER BY

You can sort by a calculated expression.

-- Sort active statuses first, completed statuses last
SELECT order_number, status, total_amount
FROM orders
ORDER BY
    CASE status
        WHEN 'pending'   THEN 1
        WHEN 'paid'      THEN 2
        WHEN 'preparing' THEN 3
        WHEN 'shipped'   THEN 4
        ELSE 5
    END,
    total_amount DESC
LIMIT 10;

Summary

Practice Problems

Problem 1

Display 'No memo' when the order's notes column is NULL. Use a CASE expression to return order_number, status, and memo (show 'No memo' if notes is NULL, otherwise show the notes value). Limit to the 15 most recent orders.

SELECT
    order_number,
    status,
    CASE
        WHEN notes IS NULL THEN 'No memo'
        ELSE notes
    END AS memo
FROM orders
ORDER BY ordered_at DESC
LIMIT 15;

Problem 2

Sort the staff list so that employees with role = 'manager' come first, then 'staff', then all others last. Within the same role, sort by name ascending. Return name, department, and role, including only active employees.

SELECT name, department, role
FROM staff
WHERE is_active = 1
ORDER BY
    CASE role
        WHEN 'manager' THEN 1
        WHEN 'staff'   THEN 2
        ELSE 3
    END,
    name ASC;

Problem 3

Convert payment methods (payments.method) to labels using a simple CASE: 'card' -> 'Credit Card', 'bank_transfer' -> 'Bank Transfer', 'cash' -> 'Cash', all others -> 'Other'. Return id, amount, and method_label, limited to 10 rows.

SELECT
    id,
    amount,
    CASE method
        WHEN 'card'          THEN 'Credit Card'
        WHEN 'bank_transfer' THEN 'Bank Transfer'
        WHEN 'cash'          THEN 'Cash'
        ELSE 'Other'
    END AS method_label
FROM payments
LIMIT 10;

Problem 4

Add a stock_status column to the product list: stock_qty = 0 -> 'Out of Stock', 1-10 -> 'Low Stock', 11-100 -> 'In Stock', over 100 -> 'Well Stocked'. Return name, stock_qty, and stock_status for all active products.

SELECT
    name,
    stock_qty,
    CASE
        WHEN stock_qty = 0         THEN 'Out of Stock'
        WHEN stock_qty <= 10       THEN 'Low Stock'
        WHEN stock_qty <= 100      THEN 'In Stock'
        ELSE 'Well Stocked'
    END AS stock_status
FROM products
WHERE is_active = 1
ORDER BY stock_qty ASC;

Problem 5

Create a generational distribution report: count how many active customers are in each generation (Gen Z: born 1997 or later, Millennial: 1981-1996, Gen X: 1965-1980, Baby Boomer+: before 1965, Unknown: birth_date is NULL). Return generation and customer_count.

SELECT
    CASE
        WHEN birth_date IS NULL THEN 'Unknown'
        WHEN CAST(SUBSTR(birth_date, 1, 4) AS INTEGER) >= 1997 THEN 'Gen Z'
        WHEN CAST(SUBSTR(birth_date, 1, 4) AS INTEGER) >= 1981 THEN 'Millennial'
        WHEN CAST(SUBSTR(birth_date, 1, 4) AS INTEGER) >= 1965 THEN 'Gen X'
        ELSE 'Baby Boomer+'
    END AS generation,
    COUNT(*) AS customer_count
FROM customers
WHERE is_active = 1
GROUP BY generation
ORDER BY customer_count DESC;

=== "MySQL"
    ```sql
    SELECT
        CASE
            WHEN birth_date IS NULL THEN 'Unknown'
            WHEN YEAR(birth_date) >= 1997 THEN 'Gen Z'
            WHEN YEAR(birth_date) >= 1981 THEN 'Millennial'
            WHEN YEAR(birth_date) >= 1965 THEN 'Gen X'
            ELSE 'Baby Boomer+'
        END AS generation,
        COUNT(*) AS customer_count
    FROM customers
    WHERE is_active = 1
    GROUP BY generation
    ORDER BY customer_count DESC;
    ```

=== "PostgreSQL"
    ```sql
    SELECT
        CASE
            WHEN birth_date IS NULL THEN 'Unknown'
            WHEN EXTRACT(YEAR FROM birth_date) >= 1997 THEN 'Gen Z'
            WHEN EXTRACT(YEAR FROM birth_date) >= 1981 THEN 'Millennial'
            WHEN EXTRACT(YEAR FROM birth_date) >= 1965 THEN 'Gen X'
            ELSE 'Baby Boomer+'
        END AS generation,
        COUNT(*) AS customer_count
    FROM customers
    WHERE is_active = 1
    GROUP BY generation
    ORDER BY customer_count DESC;
    ```

Problem 6

Convert review rating values to text labels: 5 -> 'Excellent', 4 -> 'Good', 3 -> 'Average', 2 -> 'Poor', 1 -> 'Terrible'. Find the review count and average rating per label. Return rating_label, review_count, and avg_rating sorted by avg_rating descending.

SELECT
    CASE rating
        WHEN 5 THEN 'Excellent'
        WHEN 4 THEN 'Good'
        WHEN 3 THEN 'Average'
        WHEN 2 THEN 'Poor'
        WHEN 1 THEN 'Terrible'
    END AS rating_label,
    COUNT(*)            AS review_count,
    ROUND(AVG(rating), 2) AS avg_rating
FROM reviews
GROUP BY rating
ORDER BY avg_rating DESC;

Problem 7

Classify customer point_balance into 3 tiers: 100,000+ as 'Heavy User', 10,000+ as 'Regular', others as 'Light'. Aggregate the customer count per tier for each grade. Return grade, heavy_count, regular_count, and light_count.

SELECT
    grade,
    COUNT(CASE WHEN point_balance >= 100000 THEN 1 END) AS heavy_count,
    COUNT(CASE WHEN point_balance >= 10000
                AND point_balance < 100000 THEN 1 END) AS regular_count,
    COUNT(CASE WHEN point_balance < 10000 THEN 1 END)  AS light_count
FROM customers
WHERE is_active = 1
GROUP BY grade
ORDER BY grade;

Problem 8

Aggregate order count and total revenue by order amount tier (total_amount: under 100K -> 'Small', 100K to under 500K -> 'Medium', 500K+ -> 'Large'), and sort with large orders on top. Return amount_tier, order_count, and total_revenue.

SELECT
    CASE
        WHEN total_amount < 100      THEN 'Small'
        WHEN total_amount < 500      THEN 'Medium'
        ELSE 'Large'
    END AS amount_tier,
    COUNT(*)          AS order_count,
    SUM(total_amount) AS total_revenue
FROM orders
WHERE status NOT IN ('cancelled', 'returned')
GROUP BY amount_tier
ORDER BY
    CASE
        WHEN total_amount < 100 THEN 3
        WHEN total_amount < 500 THEN 2
        ELSE 1
    END;

Problem 9

Pivot the payment outcome by method: count 'Success' (status = 'completed') and 'Failure' (all others) for each method. Return method, success_count, fail_count, and success_rate (1 decimal place), sorted by success rate descending.

SELECT
    method,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS success_count,
    COUNT(CASE WHEN status != 'completed' THEN 1 END) AS fail_count,
    ROUND(
        COUNT(CASE WHEN status = 'completed' THEN 1 END) * 100.0
        / COUNT(*),
        1
    ) AS success_rate
FROM payments
GROUP BY method
ORDER BY success_rate DESC;

Problem 10

Calculate each product's quarterly revenue for 2024 as separate columns (q1_revenue, q2_revenue, q3_revenue, q4_revenue). Use conditional aggregation (SUM(CASE WHEN ... THEN ... END)), include only products with 2024 sales, and return the top 10 by total 2024 revenue descending.

SELECT
    p.name AS product_name,
    SUM(CASE WHEN o.ordered_at BETWEEN '2024-01-01' AND '2024-03-31 23:59:59'
             THEN oi.quantity * oi.unit_price ELSE 0 END) AS q1_revenue,
    SUM(CASE WHEN o.ordered_at BETWEEN '2024-04-01' AND '2024-06-30 23:59:59'
             THEN oi.quantity * oi.unit_price ELSE 0 END) AS q2_revenue,
    SUM(CASE WHEN o.ordered_at BETWEEN '2024-07-01' AND '2024-09-30 23:59:59'
             THEN oi.quantity * oi.unit_price ELSE 0 END) AS q3_revenue,
    SUM(CASE WHEN o.ordered_at BETWEEN '2024-10-01' AND '2024-12-31 23:59:59'
             THEN oi.quantity * oi.unit_price ELSE 0 END) AS q4_revenue
FROM order_items AS oi
INNER JOIN orders    AS o ON oi.order_id   = o.id
INNER JOIN products  AS p ON oi.product_id = p.id
WHERE o.ordered_at LIKE '2024%'
  AND o.status NOT IN ('cancelled', 'returned')
GROUP BY p.id, p.name
ORDER BY (q1_revenue + q2_revenue + q3_revenue + q4_revenue) DESC
LIMIT 10;

Scoring Guide

Problem Areas:

Next: Lesson 8: INNER JOIN