Forum

Forums
SQL
Interview Questions...
"How to Use CASE St...
 
Share:
Notifications
Clear all

"How to Use CASE Statements in SQL for Conditional Logic"

 
    Last Post
RSS

Posts: 97
 SQL Admin
Admin
Topic starter
Jan 12, 2025 3:33 pm
(@sql-admin)
Estimable Member
Joined: 5 years ago

Introduction
The CASE statement in SQL is a powerful tool that allows you to perform conditional logic directly within your queries. Whether you're creating dynamic columns, handling complex business rules, or categorizing data, the CASE statement offers flexibility and efficiency. In this post, we'll explore various use cases of the CASE statement with practical examples.

Understanding the Syntax of CASE

The basic syntax of a CASE statement is as follows:

sql
 
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result_default
END
  • WHEN: Specifies a condition to evaluate.
  • THEN: Defines the result if the condition is true.
  • ELSE: Provides a default result if none of the conditions are met.
  • END: Marks the end of the CASE statement.

Example Table: Employees

EmployeeID Name Salary Department
1 Alice 7000 HR
2 Bob 9000 IT
3 Charlie 4000 Finance
4 Diana 8000 IT
5 Edward 3000 HR

1. Categorizing Salaries into Ranges

You can use a CASE statement to classify employees based on their salary range:

sql
 
SELECT Name, Salary,
CASE
WHEN Salary >= 8000 THEN 'High'
WHEN Salary BETWEEN 5000 AND 7999 THEN 'Medium'
ELSE 'Low'
END AS SalaryCategory
FROM Employees;

Output:

Name Salary SalaryCategory
Alice 7000 Medium
Bob 9000 High
Charlie 4000 Low
Diana 8000 High
Edward 3000 Low

2. Creating Dynamic Columns

You can display dynamic column values based on a condition. For example, calculate a bonus percentage based on the department:

sql
 
SELECT Name, Department,
CASE Department
WHEN 'HR' THEN 10
WHEN 'IT' THEN 15
WHEN 'Finance' THEN 20
ELSE 5
END AS BonusPercentage
FROM Employees;

Output:

Name Department BonusPercentage
Alice HR 10
Bob IT 15
Charlie Finance 20
Diana IT 15
Edward HR 10

3. Conditional Aggregation with CASE

The CASE statement can be used inside aggregate functions to compute conditional sums or counts.

For example, calculate the total salary for each department:

sql
 
SELECT Department,
SUM(CASE WHEN Salary >= 5000 THEN Salary ELSE 0 END) AS TotalHighSalaries,
SUM(CASE WHEN Salary < 5000 THEN Salary ELSE 0 END) AS TotalLowSalaries
FROM Employees
GROUP BY Department;

4. Combining CASE with ORDER BY

You can use CASE in the ORDER BY clause to sort data dynamically.

For example, sort employees by custom salary categories:

sql
 
SELECT Name, Salary
FROM Employees
ORDER BY
CASE
WHEN Salary >= 8000 THEN 1
WHEN Salary BETWEEN 5000 AND 7999 THEN 2
ELSE 3
END;

Applications of CASE in SQL

  1. Data Transformation: Categorize data on the fly.
  2. Custom Reporting: Generate dynamic reports based on business rules.
  3. Complex Queries: Simplify logic without creating multiple queries or views.
  4. Conditional Aggregation: Perform conditional counts, sums, or averages.

Conclusion
The CASE statement is a versatile tool in SQL, enabling you to handle complex conditions and dynamic logic seamlessly. By mastering its syntax and applications, you can significantly enhance your data queries and reporting capabilities. For more SQL insights and advanced techniques, visit our SQL forum.

Topic Tags
SQL CASE statement examp conditional logic in SQL SQL dynamic columns SQL conditional aggregat advanced SQL techniques

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Related Topics
Topic Tags:  SQL CASE statement examp (1) , conditional logic in SQL (2) , SQL dynamic columns (1) , SQL conditional aggregat (1) , advanced SQL techniques (3) ,
Share: