CASE Statements

The CASE statement adds conditional (if-else style) logic directly inside a SQL query - evaluating one or more conditions and returning a different value depending on which condition is met.

Basic Syntax

SELECT first_name,
    CASE
        WHEN total_spent > 50000 THEN 'High Value'
        WHEN total_spent > 10000 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS customer_segment
FROM customers;

Using CASE in Aggregations

CASE is often combined with aggregate functions to create conditional counts or sums - a common pattern for building summary reports:

SELECT
    COUNT(CASE WHEN city = 'Noida' THEN 1 END) AS noida_customers,
    COUNT(CASE WHEN city = 'Delhi' THEN 1 END) AS delhi_customers
FROM customers;

Using CASE in ORDER BY

-- Custom sort order instead of alphabetical
SELECT * FROM orders
ORDER BY CASE status
    WHEN 'Pending' THEN 1
    WHEN 'Shipped' THEN 2
    WHEN 'Delivered' THEN 3
END;