Learn SQL  /  Advanced Queries  /  CASE Expressions

CASE Expressions

CASE is SQL's if/else — it evaluates conditions and returns a different value depending on which one matches, right inside a SELECT, WHERE, or ORDER BY clause.

Basic CASE — Creating a Label Column

R Code
SELECT name, score,
  CASE
    WHEN score >= 90 THEN "A"
    WHEN score >= 75 THEN "B"
    WHEN score >= 60 THEN "C"
    ELSE "F"
  END AS grade
FROM students;
Output
name  | score | grade
Rafi  | 88    | B
Meera | 92    | A
Zayn  | 76    | B
Nadia | 65    | C

CASE checks each WHEN condition in order and returns the value from the first one that matches; ELSE is the fallback if nothing matches.

Simple CASE — Matching an Exact Value

R Code
SELECT name,
  CASE class
    WHEN "A" THEN "Advanced"
    WHEN "B" THEN "Beginner"
    ELSE "Unknown"
  END AS level
FROM students;

Conditional Aggregation with CASE

A very common, powerful pattern: use CASE inside an aggregate function to count or sum only rows meeting a condition.

R Code
SELECT
  COUNT(CASE WHEN score >= 80 THEN 1 END) AS num_passed_well,
  COUNT(CASE WHEN score < 80 THEN 1 END) AS num_others
FROM students;
Output
num_passed_well | num_others
2                | 2

Advanced: Pivoting Rows into Columns

🚀 Advanced Example
Combining CASE with SUM inside GROUP BY is how you manually pivot data - turning row values (like class) into their own summary columns, similar to a spreadsheet pivot table.
R Code
SELECT
  SUM(CASE WHEN class = "A" THEN 1 ELSE 0 END) AS class_a_count,
  SUM(CASE WHEN class = "B" THEN 1 ELSE 0 END) AS class_b_count
FROM students;
Output
class_a_count | class_b_count
2              | 2

Key Points

  • CASE WHEN condition THEN result ... ELSE default END works like if/elif/else inside a query.
  • CASE can appear in SELECT, WHERE, ORDER BY, or inside an aggregate function.
  • Wrapping CASE inside COUNT() or SUM() enables conditional counting/summing in a single query.
  • CASE + GROUP BY is the standard way to manually pivot row values into separate summary columns.