Introduction

In SQL for data analysis, you often need to apply conditions and categorize data directly within your queries. The CASE statement allows you to add conditional logic to your SQL queries, similar to if-else statements in programming. In this lesson, you will learn how to use the CASE statement in SQL for better data analysis and reporting.

What is CASE Statement in SQL

The CASE statement is used to create conditional logic in SQL queries. It evaluates conditions and returns a value when the condition is true.

Basic syntax:
SELECT column_name,
CASE
WHEN condition THEN result
WHEN condition THEN result
ELSE result
END
FROM table_name;

Example of CASE Statement

Example:
SELECT name, salary,
CASE
WHEN salary > 50000 THEN ‘High Salary’
WHEN salary BETWEEN 30000 AND 50000 THEN ‘Medium Salary’
ELSE ‘Low Salary’
END AS salary_category
FROM employees;

This query categorizes employees based on their salary.

Using CASE with Aggregate Functions

You can also use CASE with aggregate functions for advanced analysis.

Example:
SELECT
COUNT(CASE WHEN department = ‘IT’ THEN 1 END) AS it_count,
COUNT(CASE WHEN department = ‘HR’ THEN 1 END) AS hr_count
FROM employees;

This counts employees in different departments.

CASE in ORDER BY

CASE can be used to customize sorting.

Example:
SELECT name, department
FROM employees
ORDER BY
CASE
WHEN department = ‘IT’ THEN 1
WHEN department = ‘HR’ THEN 2
ELSE 3
END;

Why CASE is Important in Data Analysis

The CASE statement helps you:

• Categorize data into groups
• Apply conditional logic in queries
• Create dynamic reports
• Simplify complex analysis

Real-World Use Cases

• Categorizing customers based on purchase value
• Segmenting users by activity level
• Creating business rules in reports
• Generating dashboards with conditions

Best Practices

• Keep CASE conditions simple and readable
• Always include an ELSE condition
• Use meaningful labels for output
• Avoid deeply nested CASE statements

Common Mistakes

• Missing ELSE condition
• Writing complex and unreadable logic
• Using CASE unnecessarily

Summary

In this lesson, you learned how to use the CASE statement in SQL to apply conditional logic and categorize data. This is a powerful tool for data analysis and reporting. In the next module, you will learn SQL techniques specifically used for real-world data analysis such as data cleaning and transformation.

FAQs

1. What is CASE statement in SQL?
It is used to apply conditional logic and return values based on conditions.

2. Is CASE similar to if-else?
Yes, it works like if-else logic in programming.

3. Can CASE be used with aggregate functions?
Yes, it can be combined with functions like COUNT and SUM.

4. Where can CASE be used in SQL?
It can be used in SELECT, ORDER BY, and other clauses.

Internal Link

Want to explore more courses?
Click here for more free courses