How to Write Multiple Case Statement in SQL

SQL Case statement is used to compare an expression within one or more logical conditions by returning a value when if-then-else logics are used.

The first is a Simple Case Statement where expression is compared to static values.

CASE STATEMENT

Based on department codes and how they relate to departments, we can write a simple case statement.

–> 24         Claims

–> 36         HR

–> 40         Engineering

–> 98         Accounting

–> 103       Information Technology

–> 105       Corporate

In summary:

1. The CASE statement checks each row for conditional statement Department = 40 which is True.
2. For any given row, if the conditional statement Is true, ‘Engineering’ gets printed in the column named Department.
3. The CASE statement checks each row to see if the conditional statement Department = 24 is True.
4. For any given row, if the conditional statement Is true, ‘Claims’ gets printed in the column named Department.
5. Repeat for the rest of the conditional statements.
6. In any row for which the conditional statement is False, leaving the words ‘Corporate’ in Dept column.

At the same time all these occur, SQL is retrieving and displaying all the values in the JobID, EmployeeName, Department, StartDate, State columns.

Searched CASE Expression

The second CASE Statement is a searched CASE expression, where expression is compared to one or more logical conditions.

Syntax for Searched CASE Expression

Note: The ELSE clause is optional. “Condition” can consist of one or more logical statements.

We will be creating a grouping of StudyTimes to know if students study on Mondays Wednesday and Friday.

A Searched Case statement will be used for this logical statement.

CASE Statement

Using CASE with Aggregate Functions

To show a count of StudyNumbers by StudyTime stamps, we need an aggregate function with a case statement and a group by to group studytime stamps.

Using CASE inside Aggregate Functions

To show the total number of StudyTime and the number of Noon study time in a single row, we can do that by using an aggregation around the case statement.

Note: You can also use case statement inside a calculation. For example: To know the percentage of time that occurred during Noon hours, do the following.

Also remember CAST statement can be used to change integer variables into decimals.