Understanding SQL: When to Use CASE WHEN and GROUP BY

One of the most common questions when learning SQL is:

“Understanding SQL: When to Use CASE WHEN and GROUP BY”

They often look interchangeable — but they solve different problems.

This article explains when and why to use each, using an employee dataset, simple visuals, and practical SQL examples.

---

Sample Employee Dataset

Let’s use a simplified version of an employee table:

CREATE TABLE Employee (
    Gender VARCHAR(10),
    City VARCHAR(50),
    Salary DECIMAL(10,2),
    EverBenched VARCHAR(5),
    LeaveOrNot VARCHAR(5)
);

Sample Data

We’ll use this data throughout the article.

Gender	City	Salary	EverBenched	LeaveOrNot
Male	Toronto	80000	No	No
Female	Toronto	78000	No	Yes
Male	Vancouver	75000	Yes	No
Female	Vancouver	82000	No	No
Male	Toronto	72000	Yes	Yes

---

Core Difference

SQL Syntax Overview

1️⃣ GROUP BY Syntax

SELECT column1, AGG_FUNCTION(column2)
FROM table_name
WHERE condition
GROUP BY column1;

Example: Count employees by gender

SELECT Gender, COUNT(*) AS NumEmployees
FROM Employee
GROUP BY Gender;

• GROUP BY groups the result set by the specified column(s)
• Can be used with aggregate functions like COUNT(), AVG(), SUM(), MIN(), MAX()

---

2️⃣ CASE WHEN Syntax

SELECT
    AGG_FUNCTION(CASE WHEN condition THEN column ELSE other_value END) AS alias
FROM table_name;

Example: Count males and females in one row

SELECT
    SUM(CASE WHEN Gender = 'Male' THEN 1 ELSE 0 END) AS NumMales,
    SUM(CASE WHEN Gender = 'Female' THEN 1 ELSE 0 END) AS NumFemales
FROM Employee;

• CASE WHEN allows you to create conditional logic
• Usually combined with an aggregate function (SUM, AVG, etc.) to summarise data

---

Think of it this way:

GROUP BY  → creates ROWS
CASE WHEN → creates LOGIC

Visual Summary

GROUP BY → rows
Male   → 3
Female → 2

CASE WHEN → columns
NumMales | NumFemales
3        | 2

---

When to Use GROUP BY

Use GROUP BY When you want to split data into multiple rows based on a column.

Example: Count employees by gender

SELECT Gender, COUNT(*) AS NumEmployees
FROM Employee
GROUP BY Gender;

Result:

Gender	NumEmployees
Male	3
Female	2

Visual Representation

Employee Table
--------------
Male
Female
Male
Female
Male

GROUP BY Gender
↓
Male   → 3 rows
Female → 2 rows

Use GROUP BY when:

• You want one row per category
• Categories may change over time
• You’re building breakdowns or reports

---

When to Use CASE WHEN

Use CASE WHEN When you want to count or calculate based on conditions, you usually return columns instead of rows.

Example: Count males and females in one row

SELECT
    SUM(CASE WHEN Gender = 'Male' THEN 1 ELSE 0 END) AS NumMales,
    SUM(CASE WHEN Gender = 'Female' THEN 1 ELSE 0 END) AS NumFemales
FROM Employee;

Result:

NumMales	NumFemales
3	2

Visual Representation

Scan each row:
--------------
Male     → +1 to NumMales
Female   → +1 to NumFemales
Male     → +1 to NumMales
Female   → +1 to NumFemales
Male     → +1 to NumMales

Use CASE WHEN when:

• You want summary metrics
• You need side-by-side comparisons
• You’re building dashboards or KPIs

---

CASE WHEN for Conditional Logic

Question: Who left the company by gender?

SELECT
    SUM(CASE WHEN Gender = 'Male' AND LeaveOrNot = 'Yes' THEN 1 ELSE 0 END) AS MalesLeft,
    SUM(CASE WHEN Gender = 'Female' AND LeaveOrNot = 'Yes' THEN 1 ELSE 0 END) AS FemalesLeft
FROM Employee;

Why GROUP BY alone isn’t ideal here:

• You’re counting multiple conditions
• You want clean, readable output
• CASE lets you define logic explicitly

---

Combining CASE + GROUP BY (Real-World SQL)

This is where SQL becomes very powerful.

Example: Attrition by city and gender

SELECT City,
    SUM(CASE WHEN Gender = 'Male' AND LeaveOrNot = 'Yes' THEN 1 ELSE 0 END) AS MaleLeft,
    SUM(CASE WHEN Gender = 'Female' AND LeaveOrNot = 'Yes' THEN 1 ELSE 0 END) AS FemaleLeft
FROM Employee
GROUP BY City;

Visual Breakdown

GROUP BY City
↓
Toronto
Vancouver

CASE WHEN
↓
MaleLeft | FemaleLeft

This pattern is extremely common in analytics and interviews.

---

Salary Example: Rows vs Columns

GROUP BY version (rows)

SELECT Gender, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY Gender;

Gender	AvgSalary
Male	75666
Female	80000

CASE version (columns)

SELECT
    AVG(CASE WHEN Gender = 'Male' THEN Salary END) AS AvgMaleSalary,
    AVG(CASE WHEN Gender = 'Female' THEN Salary END) AS AvgFemaleSalary
FROM Employee;

AvgMaleSalary	AvgFemaleSalary
75666	80000

Same data — different presentation, different use case.

---

When NOT to Use CASE WHEN

Avoid CASE WHEN if:

• Categories are unknown or dynamic
• You need scalable grouping
• New values may appear often

In those cases → GROUP BY is safer.

---

Quick Decision Guide

Goal	Use
Breakdown by category	GROUP BY
One-row summary	CASE WHEN
Conditional counts	CASE WHEN
Dynamic categories	GROUP BY
Dashboards / KPIs	CASE WHEN
Analytics	CASE + GROUP BY

---

Final Takeaway

GROUP BY answers “how is my data grouped?”
CASE WHEN answers “how many meet this condition?”
Using both together is how analysts write SQL