SQL_Case_Groupby

SQLite CASE Statements, Aggregation & GROUP BY

SETUP — Table Used

-- Preview what we're working with
SELECT * FROM netflix_titles LIMIT 5;
PRAGMA table_info(netflix_titles);

Assume netflix_titles has: title_id, title, genre, rating, imdb_score, release_year, duration_mins, is_original, is_documentary, is_comedy, is_canceled, country, language

PART 1 — CASE STATEMENT BASICS

Syntax — Two Forms

-- Form 1: Searched CASE (most flexible — use this by default)
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

-- Form 2: Simple CASE (equality checks only)
CASE column
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_result
END

1. Basic Label Assignment

-- Categorize titles by IMDb score
SELECT
    title_id,
    title,
    imdb_score,
    CASE
        WHEN imdb_score >= 8.5 THEN 'Must Watch'
        WHEN imdb_score >= 7.0 THEN 'Worth Watching'
        WHEN imdb_score >= 5.5 THEN 'Average'
        ELSE                        'Skip It'
    END AS recommendation
FROM netflix_titles;

Result:

title_id	title	imdb_score	recommendation
1	Stranger Things	8.7	Must Watch
2	Emily in Paris	6.3	Average
3	Squid Game	8.0	Worth Watching

2. Simple CASE — equality checks

-- Expand rating codes into readable labels
SELECT
    title,
    rating,
    CASE rating
        WHEN 'G'     THEN 'General Audiences'
        WHEN 'PG'    THEN 'Parental Guidance'
        WHEN 'PG-13' THEN 'Parents Strongly Cautioned'
        WHEN 'R'     THEN 'Restricted'
        WHEN 'TV-MA' THEN 'Mature Audiences'
        ELSE              'Unrated'
    END AS rating_label
FROM netflix_titles;

3. CASE with Multiple Conditions in One WHEN

-- Combine score and duration into a binge-worthiness label
SELECT
    title,
    imdb_score,
    duration_mins,
    CASE
        WHEN imdb_score >= 8.0 AND duration_mins <= 60  THEN 'Easy Binge'
        WHEN imdb_score >= 8.0 AND duration_mins > 60   THEN 'Worth the Commitment'
        WHEN imdb_score < 6.0  AND duration_mins > 120  THEN 'Hard Pass'
        ELSE                                                  'Casual Watch'
    END AS binge_label
FROM netflix_titles;

⚠️ Single quotes inside strings must be escaped as '' (two single quotes)

4. CASE on Binary Y/N Columns

-- Translate Y/N flags to readable labels
SELECT
    title,
    CASE is_original    WHEN 'Y' THEN 'Netflix Original' ELSE 'Licensed'      END AS origin_type,
    CASE is_documentary WHEN 'Y' THEN 'Documentary'      ELSE 'Non-Doc'       END AS doc_status,
    CASE is_canceled    WHEN 'Y' THEN 'Canceled'         ELSE 'Still Running' END AS show_status
FROM netflix_titles;

5. Nested CASE Statements

-- Tier based on origin AND score
SELECT
    title,
    imdb_score,
    is_original,
    CASE
        WHEN is_original = 'Y' THEN
            CASE
                WHEN imdb_score >= 8.0 THEN 'Flagship Original'
                ELSE                        'Standard Original'
            END
        ELSE
            CASE
                WHEN imdb_score >= 8.0 THEN 'Top Licensed Title'
                ELSE                        'Filler Content'
            END
    END AS content_tier
FROM netflix_titles;

6. CASE in WHERE Clause

-- Apply different score thresholds based on content type
SELECT *
FROM netflix_titles
WHERE
    CASE
        WHEN is_original    = 'Y' THEN imdb_score >= 7.0   -- lower bar for originals
        WHEN is_documentary = 'Y' THEN imdb_score >= 7.5   -- higher bar for docs
        ELSE                           imdb_score >= 8.0   -- strict bar for licensed
    END;

7. CASE in ORDER BY

-- Custom sort: Originals first, then Docs, then everything else
SELECT title, genre, is_original, is_documentary
FROM netflix_titles
ORDER BY
    CASE
        WHEN is_original    = 'Y' THEN 1
        WHEN is_documentary = 'Y' THEN 2
        WHEN is_canceled    = 'Y' THEN 4
        ELSE                           3
    END;

PART 2 — CASE WITH AGGREGATION

8. Binary Indicator → Count (the core pattern)

-- Count titles with each flag set to 'Y'
SELECT
    SUM(CASE WHEN is_original    = 'Y' THEN 1 ELSE 0 END) AS original_count,
    SUM(CASE WHEN is_documentary = 'Y' THEN 1 ELSE 0 END) AS documentary_count,
    SUM(CASE WHEN is_comedy      = 'Y' THEN 1 ELSE 0 END) AS comedy_count,
    SUM(CASE WHEN is_canceled    = 'Y' THEN 1 ELSE 0 END) AS canceled_count,
    COUNT(*)                                               AS total_titles
FROM netflix_titles;

9. Binary Indicator → Percentage

-- Percentage of titles with each binary flag
SELECT
    SUM(CASE WHEN is_original    = 'Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS pct_original,
    SUM(CASE WHEN is_documentary = 'Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS pct_documentary,
    SUM(CASE WHEN is_canceled    = 'Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS pct_canceled
FROM netflix_titles;

⚠️ Multiply by 100.0 not 100 — SQLite does integer division by default. 3 / 4 = 0 but 3 * 100.0 / 4 = 75.0

10. Conditional SUM — sum only matching rows

-- Total watch minutes split by original vs licensed content
SELECT
    SUM(CASE WHEN is_original = 'Y' THEN duration_mins ELSE 0 END) AS original_minutes,
    SUM(CASE WHEN is_original = 'N' THEN duration_mins ELSE 0 END) AS licensed_minutes,
    SUM(duration_mins)                                              AS total_minutes
FROM netflix_titles;

11. Conditional AVG

-- Average IMDb score for originals vs licensed titles
SELECT
    AVG(CASE WHEN is_original = 'Y' THEN imdb_score END) AS avg_score_original,
    AVG(CASE WHEN is_original = 'N' THEN imdb_score END) AS avg_score_licensed
FROM netflix_titles;

When ELSE is omitted, non-matching rows return NULL. AVG() ignores NULLs automatically — so omitting ELSE is cleaner than ELSE 0 for averages (0 would wrongly pull the average down).

-- ✅ Correct — ELSE omitted, NULLs excluded from AVG
AVG(CASE WHEN is_original = 'Y' THEN imdb_score END)

-- ❌ Wrong — ELSE 0 pulls the average toward 0 incorrectly
AVG(CASE WHEN is_original = 'Y' THEN imdb_score ELSE 0 END)

12. Pivot Table Pattern — rows into columns

-- Count titles per genre broken out by original vs licensed
SELECT
    genre,
    SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END) AS original_count,
    SUM(CASE WHEN is_original = 'N' THEN 1 ELSE 0 END) AS licensed_count,
    COUNT(*)                                            AS total
FROM netflix_titles
GROUP BY genre;

Result:

genre	original_count	licensed_count	total
Drama	42	31	73
Comedy	28	19	47
Documentary	15	22	37

PART 3 — CASE WITH GROUP BY

13. GROUP BY a CASE expression — group by derived category

-- Group titles by score tier and count each tier
SELECT
    CASE
        WHEN imdb_score >= 8.5 THEN 'Must Watch'
        WHEN imdb_score >= 7.0 THEN 'Worth Watching'
        WHEN imdb_score >= 5.5 THEN 'Average'
        ELSE                        'Skip It'
    END AS recommendation,
    COUNT(*)              AS title_count,
    AVG(imdb_score)       AS avg_score,
    AVG(duration_mins)    AS avg_duration
FROM netflix_titles
GROUP BY
    CASE
        WHEN imdb_score >= 8.5 THEN 'Must Watch'
        WHEN imdb_score >= 7.0 THEN 'Worth Watching'
        WHEN imdb_score >= 5.5 THEN 'Average'
        ELSE                        'Skip It'
    END;

In SQLite you must repeat the full CASE in GROUP BY. You cannot reference the alias recommendation in GROUP BY.

14. GROUP BY CASE + Binary Percentage per Group

-- For each score tier: count, avg score, and % that are Netflix Originals
SELECT
    CASE
        WHEN imdb_score >= 8.5 THEN 'Must Watch'
        WHEN imdb_score >= 7.0 THEN 'Worth Watching'
        WHEN imdb_score >= 5.5 THEN 'Average'
        ELSE                        'Skip It'
    END AS recommendation,
    COUNT(*)                                                           AS total,
    ROUND(AVG(imdb_score), 2)                                          AS avg_score,
    SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END)                AS original_count,
    SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END) * 100.0
        / COUNT(*)                                                     AS pct_original
FROM netflix_titles
GROUP BY
    CASE
        WHEN imdb_score >= 8.5 THEN 'Must Watch'
        WHEN imdb_score >= 7.0 THEN 'Worth Watching'
        WHEN imdb_score >= 5.5 THEN 'Average'
        ELSE                        'Skip It'
    END
ORDER BY avg_score DESC;

15. Regular GROUP BY + CASE aggregation

-- Per genre: total titles, % originals, % canceled, avg score by origin type
SELECT
    genre,
    COUNT(*)                                                                AS total,
    SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS pct_original,
    SUM(CASE WHEN is_canceled = 'Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS pct_canceled,
    AVG(CASE WHEN is_original = 'Y' THEN imdb_score END)                   AS avg_score_originals,
    AVG(CASE WHEN is_original = 'N' THEN imdb_score END)                   AS avg_score_licensed
FROM netflix_titles
GROUP BY genre
ORDER BY total DESC;

16. HAVING with CASE aggregation

-- Only show genres where more than 40% of titles are Netflix Originals
SELECT
    genre,
    COUNT(*) AS total,
    SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS pct_original
FROM netflix_titles
GROUP BY genre
HAVING SUM(CASE WHEN is_original = 'Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) > 40
ORDER BY pct_original DESC;

17. UNION ALL + CASE — binary indicators as rows (exam pattern)

-- One row per indicator showing percentage of Y values
SELECT 'Is Original'    AS status,
    SUM(CASE WHEN is_original    = 'Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS percentage
FROM netflix_titles
UNION ALL
SELECT 'Is Documentary',
    SUM(CASE WHEN is_documentary = 'Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)
FROM netflix_titles
UNION ALL
SELECT 'Is Comedy',
    SUM(CASE WHEN is_comedy      = 'Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)
FROM netflix_titles
UNION ALL
SELECT 'Is Canceled',
    SUM(CASE WHEN is_canceled    = 'Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)
FROM netflix_titles;

PART 4 — SUBQUERY WITH CASE

18. Use CASE result in outer query filter

-- Filter using a derived CASE column via subquery
SELECT *
FROM (
    SELECT
        title_id,
        title,
        imdb_score,
        CASE
            WHEN imdb_score >= 8.5 THEN 'Must Watch'
            WHEN imdb_score >= 7.0 THEN 'Worth Watching'
            ELSE                        'Other'
        END AS recommendation
    FROM netflix_titles
)
WHERE recommendation = 'Must Watch';

🧠 Quick Reference

Goal	Pattern
Label rows by condition	`CASE WHEN ... THEN ... ELSE ... END`
Equality check shorthand	`CASE col WHEN val THEN ... END`
Count matching rows	`SUM(CASE WHEN col='Y' THEN 1 ELSE 0 END)`
Percentage of matching	`SUM(CASE WHEN col='Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)`
Conditional average	`AVG(CASE WHEN col='Y' THEN numeric_col END)`
Conditional sum	`SUM(CASE WHEN col='Y' THEN numeric_col ELSE 0 END)`
Pivot columns	`SUM(CASE WHEN col='A' THEN 1 ELSE 0 END) AS col_a`
Group by derived tier	`GROUP BY CASE WHEN ... END` (repeat full expression)
Filter groups by CASE	`HAVING SUM(CASE ...) > threshold`
Rows per indicator	`UNION ALL` + one `SELECT` per indicator

🧠 Mental Model

CASE is just an IF/ELSE that works inside SQL expressions.
It can go anywhere an expression is valid:

  SELECT   → create a new column
  WHERE    → conditional filtering
  GROUP BY → group by a derived category
  HAVING   → filter groups by conditional aggregate
  ORDER BY → custom sort order

Key rules:
  1. WHEN conditions checked TOP TO BOTTOM — first match wins
  2. Always include ELSE — without it unmatched rows return NULL
  3. For AVG: omit ELSE so NULLs are excluded automatically
     For SUM/COUNT: use ELSE 0 to make intent explicit
  4. In GROUP BY: repeat the full CASE — aliases not allowed in SQLite
  5. Multiply by 100.0 not 100 for percentages — avoid integer division

sreekarun/SQL_case.md

Select an option

No results found

Select an option

No results found

SQLite CASE Statements, Aggregation & GROUP BY

SETUP — Table Used

PART 1 — CASE STATEMENT BASICS

Syntax — Two Forms

1. Basic Label Assignment

2. Simple CASE — equality checks

3. CASE with Multiple Conditions in One WHEN

4. CASE on Binary Y/N Columns

5. Nested CASE Statements

6. CASE in WHERE Clause

7. CASE in ORDER BY

PART 2 — CASE WITH AGGREGATION

8. Binary Indicator → Count (the core pattern)

9. Binary Indicator → Percentage

10. Conditional SUM — sum only matching rows

11. Conditional AVG

12. Pivot Table Pattern — rows into columns

PART 3 — CASE WITH GROUP BY

13. GROUP BY a CASE expression — group by derived category

14. GROUP BY CASE + Binary Percentage per Group

15. Regular GROUP BY + CASE aggregation

16. HAVING with CASE aggregation

17. UNION ALL + CASE — binary indicators as rows (exam pattern)

PART 4 — SUBQUERY WITH CASE

18. Use CASE result in outer query filter

🧠 Quick Reference

🧠 Mental Model