Just in CASE

I recently implemented a feature that required using the SQL CASE statement. Thus, I decided to write a post on the exciting capabilities of the SQL CASE statement. I would be using sample student result data which you can generate by running the SQL in this gist. Note that PostgreSQL is RDBMS used in this post but the CASE statement is similar in other RDBMS

What is the CASE statement

The CASE statement is the SQL way of creating conditional statements(if-else-then) when querying for column values. Here is the general structure of a CASE statement

CASE 
     WHEN {condition1} THEN {value1}
     WHEN {condition2} THEN {value2}
                  .
                  .
                  .
     WHEN {conditionN} THEN {valueN}
END

The CASE statement comes in handy when:

• Categorizing data
• Filtering data
• Aggregating data
• Updating data

I would explain each use case in subsequent sections using the student results data

Categorising data

Let's say we have a table results that contains student results in several courses as follows

If the pass mark for each course is 60 then we can use the CASE statement to categorize this data by displaying "Passed" when the student passes and "Failed" when the student fails. Here is how it looks like:

SELECT  subject, student_id, score, 
    CASE 
        WHEN score >= 60 THEN 'Passed'
        ELSE 'Failed'
    END AS status
FROM results 

ORDER BY subject, score, status;

This would produce the following results:

Now we can see which student passed or failed each subject.

The CASE statement does not affect our other parts of our select statement above(although it can be used in other parts), thus we can decide to view the people that have passed/failed Statistics as follows:

SELECT student_id, subject, score, 
    CASE 
        WHEN score >= 60 THEN 'Passed'
        ELSE 'Failed'
    END AS status
FROM results 

WHERE subject = 'Statistics'
ORDER BY score
;

We get the following results

Now it is easy to see that there are quite a number of students that failed Statistics.

Adding more conditions to the CASE statement

Let's say we want to retrieve the grades(A, B, C,D, F) for each student in the Statistics course such that

an A is 90-100, a B is 80-89, a C is 70-79, a D is 60-69 and an F is <60. We can achieve that by using AND operator in the conditional part of our CASE statement as follows:

SELECT student_id, subject, score,  
    CASE 
        WHEN score >= 70 AND  score <= 79 THEN 'C'
        WHEN score >= 60 AND score <= 69 THEN 'D'
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 AND score <= 89  THEN 'B'

        ELSE 'F'
    END AS grade
FROM results
WHERE subject = 'Statistics'
ORDER BY score DESC
;
;

You could also use the OR operator within the CASE statement. Also, note that the CASE statement checks the conditions in the order in which they were created thus the following CASE statement does the same thing as before:

CASE 
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    WHEN score >= 60 THEN 'D'


    ELSE 'F'
END AS grade

Aggregating data

COUNT

Let us say we want to retrieve the number of students that passed and the number that failed each subject. We can do that using the following query:

SELECT subject,

    COUNT(CASE 
        WHEN score >= 60 THEN 'Passed'
    END ) as number_of_success,

    COUNT(CASE 
        WHEN score < 60 THEN 'Failed'
    END ) as number_of_failure
FROM results 


GROUP BY subject
ORDER BY subject;

SUM

We can also get a similar result using the SUM function. This time we would be returning numbers (in this case 1 when the student passes and 0 otherwise) to the SUM function so that it can add all the numbers together.

SELECT subject,

    SUM(
        CASE 
            WHEN score >= 60 THEN 1
            ELSE 0
        END ) as number_of_success,

    SUM(
        CASE 
            WHEN score < 60 THEN 1
            ELSE 0
        END ) as number_of_failure
FROM results 


GROUP BY subject
ORDER BY subject;

Gives exactly the same result as before.

AVG

The CASE statement can be used along with the AVG function to get the percentage of a set of values. For example, we can use the following query to get what percentage of students passed and failed each subject:

SELECT subject,

    AVG(
        CASE 
            WHEN score >= 60 THEN 1
            ELSE 0
        END )* 100 AS percentage_passed,

    AVG(
        CASE 
            WHEN score < 60 THEN 1
            ELSE 0
        END )* 100 AS percentage_failed
FROM results 


GROUP BY subject;

To make the result more readable let us round to 2 d.p:

SELECT subject,

    ROUND(AVG(
        CASE 
            WHEN score >= 60 THEN 1
            ELSE 0
        END )* 100 , 2) AS percentage_passed,

    ROUND(AVG(
        CASE 
            WHEN score < 60 THEN 1
            ELSE 0
        END )* 100 , 2) AS percentage_failed
FROM results 


GROUP BY subject
ORDER BY subject;

Any other aggregation function

You can use any other aggregation function with the CASE statement to get any result that requires some form of conditional operation.

Updating Data

Let's take a look at the students that passed/failed 'Chemistry' once again.

SELECT student_id, subject, score, 
    CASE 
        WHEN score >= 60 THEN 'Passed'
        ELSE 'Failed'
    END AS status
FROM results 

WHERE subject = 'Chemistry'
ORDER BY score
;

If the students with ids 12, 10, 11 and 15 re-took the course and had the following scores

Now we have to update the Chemistry table with this new result. One way we can do this is to use multiple UPDATE statements like so:

UPDATE results
    SET score = 70
    WHERE student_id = 12 AND subject = 'Chemistry';

UPDATE results
    SET score = 80
    WHERE student_id = 10 AND subject = 'Chemistry';

         .
         .
         .

But this can be quite inefficient and might be a little difficult to maintain. You can perform multiple updates with different values using the CASE statement to specify a value for each student_id and an IN clause to select all the student_ids you want like so:

UPDATE results
    SET score = 
        CASE 
            WHEN student_id = 12 THEN 70
            WHEN student_id = 10 THEN 80
            WHEN student_id = 11 THEN 85
            WHEN student_id = 15 THEN 90
        END
    WHERE student_id IN (12, 10, 11, 15) AND subject = 'Chemistry';

Now when we retrieve the data for 'Chemistry'

We can see that the students we specified were updated successfully.

Note that while doing an update and the data type returned in the CASE statement does not match the data type of the column, we would need to do an explicit cast. For example, if we had returned strings in our case statement previously

UPDATE results
    SET score = 
        CASE 
            WHEN student_id = 12 THEN '70'
            WHEN student_id = 10 THEN '80'
            WHEN student_id = 11 THEN '85'
            WHEN student_id = 15 THEN '90'
        END
    WHERE student_id IN (12, 10, 11, 15) AND subject = 'Chemistry';

Then Postgresql would have thrown the following error

ERROR:  syntax error at or near "score"
LINE 2: SET SET score = 

To solve this we just cast the values in the case to INT like so:

UPDATE results
    SET score = 
        CASE 
            WHEN student_id = 12 THEN '70'
            WHEN student_id = 10 THEN '80'
            WHEN student_id = 11 THEN '85'
            WHEN student_id = 15 THEN '90'
        END::int
    WHERE student_id IN (12, 10, 11, 15) AND subject = 'Chemistry';

Works just like before!

Conclusion

So we have seen that the CASE statement can come in handy for categorizing, aggregating and updating data. So in case you ever want to perform a conditional statement in SQL, always remember to use the CASE statement