CASE Use Cases: Categorise, Map, Conditional Aggregation

CASE is one of the most-used tools in real analytics work. This topic collects its classic use cases on IPL data: categorising, mapping / standardising, handling NULLs, custom sorting, and — the big one — conditional aggregation.

Definition

Conditional aggregation means putting a CASE expression inside an aggregate function (SUM, COUNT, AVG) so that only the rows meeting a condition contribute to the result. It lets one query return several filtered totals side by side.

Syntax — CASE inside an aggregate

SELECT
    group_column,
    SUM(CASE WHEN condition THEN 1 ELSE 0 END)          AS count_if,
    SUM(CASE WHEN condition THEN value_col ELSE 0 END)  AS sum_if
FROM table_name
GROUP BY group_column;

Example — conditional counting

SELECT
    country,
    COUNT(*) AS players,
    SUM(CASE WHEN runs >= 500 THEN 1 ELSE 0 END) AS big_scorers
FROM players
GROUP BY country;

How it works: step 1 — CASE marks each row 1 (500+ runs) or 0; step 2 — SUM adds up the 1s per country. India has 4 players, but only Kohli (750) and Rohit (500) are 500+ scorers. (Aggregates and GROUP BY are covered fully in the next chapter.)

Tip: COUNT(CASE WHEN runs >= 500 THEN 1 END) gives the same answer — with no ELSE the non-matching rows become NULL, and COUNT(column) ignores NULLs.

Example — conditional summing (a mini pivot)

SELECT
    player,
    SUM(CASE WHEN MONTH(match_date) = 3 THEN runs ELSE 0 END) AS march_runs,
    SUM(CASE WHEN MONTH(match_date) = 4 THEN runs ELSE 0 END) AS april_runs
FROM innings
GROUP BY player
ORDER BY player;

Rows become columns: each CASE lets only one month's runs into its sum. Dhoni's NULL innings adds nothing — SUM ignores NULL.

Example — multi-condition categories with NULL handling

SELECT
    player,
    match_date,
    runs,
    CASE
        WHEN runs IS NULL THEN 'Did not bat'
        WHEN runs >= 100  THEN 'Century'
        WHEN runs >= 50   THEN 'Fifty'
        WHEN runs = 0     THEN 'Duck'
        ELSE 'Start'
    END AS milestone
FROM innings
ORDER BY id;

The IS NULL check comes first so NULL runs get a proper label instead of falling to ELSE.

Example — conditions on several columns

SELECT
    name,
    country,
    CASE
        WHEN country = 'India' AND runs >= 500 THEN 'Indian star'
        WHEN country <> 'India'                THEN 'Overseas'
        ELSE 'Indian squad'
    END AS bucket
FROM players;

Example — custom sort order

SELECT name, runs
FROM players
ORDER BY
    CASE WHEN team = 'MI' THEN 0 ELSE 1 END,
    runs DESC;

MI players get sort key 0 and come first; everyone else gets 1. Inside each group, runs decide.

Common mistakes

• ❌ SUM(CASE WHEN runs >= 500 THEN 1 END) returns NULL (not 0) for a group with no matches. ✅ Add ELSE 0.
• ❌ COUNT(CASE WHEN runs >= 500 THEN 1 ELSE 0 END) — COUNT counts the 0s too! ✅ Use SUM with ELSE 0, or COUNT without ELSE.
• ❌ Repeating a long CASE in GROUP BY with a typo — the two must match exactly. ✅ Copy it exactly (or use a subquery/CTE, coming later).
• ❌ Putting the NULL check last — NULL rows fall into ELSE first.

Interview tip

Q: Count how many players in each country scored 500+ runs, in a single query. SELECT country, SUM(CASE WHEN runs >= 500 THEN 1 ELSE 0 END) FROM players GROUP BY country; — this conditional aggregation pattern is asked constantly, because it replaces several separate filtered queries with one pass over the table.