CASE WHEN SQL

The CASE WHEN SQL statement is a conditional statement that allows you to introduce logic into SQL queries. This feature can be compared to the “if-else” conditional statement in programming languages like Python or C#.

CASE WHEN syntax in SQL

The CASE WHEN syntax in SQL is quite straightforward. It follows the following pattern:

CASE
   WHEN condition1 THEN result1
   WHEN condition2 THEN result2
   ...
   ELSE result
END;

When the SQL Server processes this SQL CASE WHEN statement, it evaluates each condition from top to bottom. If a condition evaluates to true, the SQL Server will return the corresponding result and cease further evaluation. If none of the conditions in the SQL CASE WHEN statement is satisfied, the server returns the "ELSE" result. If no "ELSE" is specified, it will return NULL.

Let's illustrate the concept with an example. Consider we have a 'students' table with 'Grade' and 'Name' fields.

We want to categorize the students based on their Grades. The SQL CASE WHEN Statement would look like this:

SELECT Name, 
       CASE
           WHEN Grade >= 90 THEN 'A'
           WHEN Grade >= 80 THEN 'B'
           WHEN Grade >= 70 THEN 'C'
           ELSE 'D'
       END AS GradeCategory
FROM students;

The output table for the above SQL CASE WHEN example would be:

The CASE WHEN SQL statement is often used in the SELECT clause. The CASE WHEN in SQL SELECT statement allows us to create new fields based on conditional logic.

Let's enhance our previous example by adding a 'Status' column that denotes whether a student has passed or failed.

SELECT Name, Grade,
       CASE
           WHEN Grade >= 70 THEN 'Passed'
           ELSE 'Failed'
       END AS Status
FROM students;

The output table for this SQL CASE WHEN query would look like this:

The CASE WHEN SQL statement allows you to handle multiple conditions within the same query. This is akin to using multiple if-then-else statements in traditional programming languages. Although we saw a similar feature in the first example, but here we will see a little enhanced version.

Consider an enhanced students table with a 'Gender' field:

Suppose we want to categorize the students not only by their Grades but also by their Gender. We could achieve this with the following SQL query:

SELECT Name, Grade, Gender,
       CASE
           WHEN Grade >= 80 AND Gender = 'Male' THEN 'High scoring male'
           WHEN Grade >= 80 AND Gender = 'Female' THEN 'High scoring female'
           WHEN Grade < 80 AND Gender = 'Male' THEN 'Low scoring male'
           WHEN Grade < 80 AND Gender = 'Female' THEN 'Low scoring female'
       END AS Category
FROM students;

The output for above SQL CASE WHEN query would be:

We can use the CASE WHEN SQL statement together with the ORDER BY clause to order our data based on complex conditions. This feature, which we can call SQL ORDER BY CASE WHEN, can be useful when we need to sort data in a non-standard way.

Consider the following scenario: We want to sort the students based on whether they passed or failed, and within these groups, we want to sort them by their Grades. This can be accomplished using the following CASE WHEN SQL query:

SELECT Name, Grade,
       CASE
           WHEN Grade >= 70 THEN 'Passed'
           ELSE 'Failed'
       END AS Status
FROM students
ORDER BY Status DESC, Grade DESC;

The output for the above CASE WHEN SQL query would be:

Here, the 'Passed' students are shown first because 'Passed' is greater than 'Failed' in lexicographical order, and within the 'Passed' group, students are sorted in descending order of Grades.

The CASE WHEN SQL statement can also be used with aggregate functions to calculate statistics based on conditions. This feature allows us to perform complex calculations within our SQL queries.

Suppose we want to calculate the average Grade for the students who passed and failed. We could use the following SQL query (we will learn more about GROUP BY clause used in this query in the next section):

SELECT 
    CASE
        WHEN Grade >= 70 THEN 'Passed'
        ELSE 'Failed'
    END AS Status,
    AVG(Grade) AS Average_Grade
FROM students
GROUP BY
    CASE
        WHEN Grade >= 70 THEN 'Passed'
        ELSE 'Failed'
    END;

This CASE WHEN SQL would return a table like:

The GROUP BY CASE WHEN SQL feature allows us to categorize and group our data based on conditions. It's a useful tool when we need to aggregate data into categories that don't exist as distinct values in our dataset.

For example, if we want to count how many male and female students passed or failed. We could use the following SQL query:

SELECT 
    CASE
        WHEN Grade >= 70 THEN 'Passed'
        ELSE 'Failed'
    END AS Status,
    Gender,
    COUNT(*) AS Count
FROM students
GROUP BY
    CASE
        WHEN Grade >= 70 THEN 'Passed'
        ELSE 'Failed'
    END,
    Gender;

This CASE WHEN SQL query would return a table like:

The HAVING CASE WHEN SQL feature allows us to filter the results of a query after the aggregation has taken place. This is particularly used when we want to apply conditions to aggregated data.

Let's say we only need to display the categories where the count of students is more than 1. We can modify the previous CASE WHEN SQL query like so:

SELECT 
   CASE
       WHEN grade >= 70 THEN 'Passed'
       ELSE 'Failed'
   END AS Status,
   Gender,
   COUNT(*) AS Count
FROM students
GROUP BY
   CASE
       WHEN Grade >= 70 THEN 'Passed'
       ELSE 'Failed'
   END,
   Gender
HAVING COUNT(*) > 1;

This Select Case When In SQL using HAVING clause would return a table like: