CASE & Conditional Aggregation in SQL

If you ask any working data analyst in India what SQL feature they reach for most often, CASE will be near the top of the list. It is the closest thing SQL has to an "if-else", and once you combine it with aggregate functions like SUM, COUNT, and AVG, you can turn messy transactional tables into clean, report-ready summaries without leaving the database.

This guide walks through CASE in SELECT, then the real power move: conditional aggregation (pivoting rows into columns). Examples use the kind of data you would actually see at a Zomato, Flipkart, or your neighbourhood kirana store.

The CASE Expression in 60 Seconds

CASE has two forms. The searched form (most flexible) evaluates conditions top to bottom and returns the first match:

SELECT
    order_id,
    order_amount,
    CASE
        WHEN order_amount >= 5000 THEN 'High Value'
        WHEN order_amount >= 1000 THEN 'Mid Value'
        ELSE 'Low Value'
    END AS order_tier
FROM orders;

The simple form compares one expression to several values:

SELECT
    payment_mode,
    CASE payment_mode
        WHEN 'UPI'  THEN 'Digital'
        WHEN 'Card' THEN 'Digital'
        WHEN 'COD'  THEN 'Cash'
        ELSE 'Other'
    END AS payment_group
FROM orders;

Key rules to remember:

• Conditions are evaluated in order. Put the most specific (narrowest) condition first.
• If no WHEN matches and there is no ELSE, CASE returns NULL.
• Every branch must return a compatible data type. Mixing text and numbers will error or implicitly cast.

CASE Inside SELECT: Labels and Buckets

The most common everyday use is bucketing a continuous value. Say you want to segment Flipkart customers by their lifetime spend:

SELECT
    customer_id,
    SUM(order_amount) AS lifetime_value,
    CASE
        WHEN SUM(order_amount) >= 50000 THEN 'Platinum'
        WHEN SUM(order_amount) >= 20000 THEN 'Gold'
        WHEN SUM(order_amount) >= 5000  THEN 'Silver'
        ELSE 'Bronze'
    END AS customer_segment
FROM orders
GROUP BY customer_id;

Notice you can use an aggregate (SUM(order_amount)) inside CASE as long as you are in a grouped query. The CASE runs after aggregation, so it sees the grouped total.

Conditional Aggregation: The Real Superpower

Here is the trick that separates beginners from analysts. By putting CASE inside an aggregate, you can count or sum only the rows that match a condition. The pattern is:

SUM(CASE WHEN <condition> THEN <value> ELSE 0 END)
COUNT(CASE WHEN <condition> THEN 1 END)

For SUM, non-matching rows contribute 0. For COUNT, non-matching rows return NULL, and COUNT ignores NULL - so you do not even need an ELSE.

Example: Payment Mode Breakdown in One Row

Instead of running three separate queries, get a single summary row per day:

SELECT
    order_date,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN payment_mode = 'UPI' THEN order_amount ELSE 0 END) AS upi_revenue,
    SUM(CASE WHEN payment_mode = 'Card' THEN order_amount ELSE 0 END) AS card_revenue,
    SUM(CASE WHEN payment_mode = 'COD' THEN order_amount ELSE 0 END) AS cod_revenue,
    COUNT(CASE WHEN payment_mode = 'UPI' THEN 1 END) AS upi_orders
FROM orders
GROUP BY order_date
ORDER BY order_date;

This is a pivot done in plain SQL. Rows (one per payment mode) become columns. No special PIVOT syntax needed, and it works in PostgreSQL, MySQL, SQL Server, and BigQuery alike.

Example: Monthly Sales by City

A classic interview and dashboard task - sales per city, one column per month:

SELECT
    city,
    SUM(CASE WHEN MONTH(order_date) = 1 THEN order_amount ELSE 0 END) AS jan_sales,
    SUM(CASE WHEN MONTH(order_date) = 2 THEN order_amount ELSE 0 END) AS feb_sales,
    SUM(CASE WHEN MONTH(order_date) = 3 THEN order_amount ELSE 0 END) AS mar_sales
FROM orders
WHERE YEAR(order_date) = 2025
GROUP BY city;

A kirana owner could read this instantly: Mumbai did ₹4.2L in Jan, Pune ₹1.1L, and so on.

Computing Rates and Percentages

Combine conditional COUNT with COUNT(*) to get conversion or success rates. Use AVG of a 0/1 flag as a neat shortcut:

SELECT
    delivery_partner,
    COUNT(*) AS total_deliveries,
    AVG(CASE WHEN status = 'Delivered' THEN 1.0 ELSE 0 END) AS success_rate
FROM deliveries
GROUP BY delivery_partner;

AVG of a 1/0 column equals the fraction of 1s - your success rate, directly. Note the 1.0 (not 1) to force floating-point division and avoid integer truncation, a trap in MySQL and SQL Server.

Common Pitfalls

These bugs quietly produce wrong numbers, which is worse than an error.

• Forgetting ELSE in SUM. SUM(CASE WHEN x THEN amount END) works because SUM ignores NULL, but mixing this habit with arithmetic later can surprise you. Being explicit with ELSE 0 is safer and more readable.
• Overlapping conditions. If two WHEN branches can both be true, only the first fires. Order them deliberately.
• Integer division. COUNT(...)/COUNT(*) returns 0 in many engines because both are integers. Multiply by 1.0 or CAST to decimal.
• Counting with ELSE 0 in COUNT. COUNT(CASE WHEN x THEN 1 ELSE 0 END) counts every row, because 0 is not NULL. Drop the ELSE when using COUNT, or switch to SUM.
• NULLs in the test column. A row where payment_mode IS NULL matches no WHEN, so it lands in ELSE. Decide consciously whether that is correct.

Best Practices

• Prefer conditional aggregation over multiple round-trips to the database - one scan instead of many.
• Alias every computed column clearly (upi_revenue, not col1) so dashboards and downstream code stay readable.
• For very wide pivots (12 months, 30 cities), generate the SQL programmatically rather than hand-typing each branch.
• Keep the CASE logic in SQL when it is reporting logic; push it to the BI layer (Power BI, Looker) only when it is presentation logic.

CASE plus aggregation is one of the highest-leverage skills in analyst SQL. Master the SUM(CASE ... END) pattern and you will reach for it in nearly every reporting query you write.

Related: SQL + Power BI Challenge for Data Analysts · Practise SQL problems

Don't just read. Prove your skill on DevWithData.