Just in CASE
Tags: sql, postgres
Published: 2019-11-20 11:14:06

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](https://gist.github.com/chidioguejiofor/952a5a77157f6032052339ba6dcaf557). 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

```sql
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

 student_id |      subject       | score 
------------+--------------------+-------
          1 | Arts               |    49
          1 | Biology            |     0
          1 | Chemistry          |     0
          1 | Computer Science   |    74
          1 | Economics          |    73
          1 | English Language   |    63
          1 | History            |    72
          1 | Literature         |     0
          1 | Mathematics        |     0
          1 | Physical Education |    88
          1 | Physics            |    71
          1 | Statistics         |    14
          2 | Arts               |    76
          2 | Biology            |    78
          2 | Chemistry          |    73
          2 | Commerce           |    76
          2 | Computer Science   |    77
          2 | Economics          |    81
          2 | English Language   |     0
          2 | History            |    71
          2 | Literature         |    73
          2 | Mathematics        |    66
          2 | Physical Education |    70
          2 | Physics            |    73
          2 | Statistics         |    91
          4 | Arts               |    70
          4 | Biology            |    77
          4 | Chemistry          |    71
          4 | Commerce           |    66
          4 | Computer Science   |    52
          4 | Economics          |    70
          4 | English Language   |     0
          4 | History            |    78
          4 | Literature         |    77
          4 | Mathematics        |    71
          4 | Physical Education |    70
          4 | Physics            |    80
 . |            .  | . 
 . |            .  | . 
 . |            .  | .              
 . |            .  | . 

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:

```sql
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:


      subject       | student_id | score | status 
--------------------+------------+-------+--------
 Arts               |         12 |    45 | Failed
 Arts               |         21 |    47 | Failed
 Arts               |          1 |    49 | Failed
 Arts               |         20 |    50 | Failed
 Arts               |         23 |    67 | Passed
 Arts               |         16 |    67 | Passed
 Arts               |          6 |    69 | Passed
 Arts               |         17 |    70 | Passed
 Arts               |         11 |    70 | Passed
 Arts               |          4 |    70 | Passed
 Arts               |         22 |    70 | Passed
 Arts               |         19 |    71 | Passed
 Arts               |         15 |    71 | Passed
 Arts               |          8 |    71 | Passed
 Arts               |          5 |    72 | Passed
 Arts               |          7 |    75 | Passed
 Arts               |          2 |    76 | Passed
 Arts               |         18 |    76 | Passed
 Arts               |         13 |    77 | Passed
 Arts               |         10 |    79 | Passed
 Arts               |         14 |    80 | Passed
 Arts               |          9 |    80 | Passed
 Arts               |         24 |    83 | Passed
 Biology            |         23 |     0 | Failed
 Biology            |         21 |     0 | Failed
 Biology            |         12 |     0 | Failed
 Biology            |          1 |     0 | Failed
 Biology            |         15 |     0 | Failed
 Biology            |         20 |    55 | Failed
 Biology            |         22 |    56 | Failed
 Biology            |         11 |    57 | Failed
 Biology            |          6 |    63 | Passed
 Biology            |          5 |    64 | Passed
 Biology            |          8 |    65 | Passed
 Biology            |         13 |    65 | Passed
 Biology            |         10 |    66 | Passed
 Biology            |         16 |    70 | Passed
 Biology            |         19 |    71 | Passed
 Biology            |         18 |    73 | Passed
 Biology            |         14 |    74 | Passed
 Biology            |         17 |    77 | Passed
 Biology            |          4 |    77 | Passed
 Biology            |          2 |    78 | Passed
 Biology            |          7 |    81 | Passed
 Biology            |          9 |    84 | Passed
 Biology            |         24 |    85 | Passed
 Chemistry          |         21 |     0 | Failed
 Chemistry          |          1 |     0 | Failed
 Chemistry          |         23 |    36 | Failed
 Chemistry          |         20 |    40 | Failed
 Chemistry          |         12 |    40 | Failed
 Chemistry          |         22 |    41 | Failed
 Chemistry          |         15 |    45 | Failed
 Chemistry          |         10 |    51 | Failed
 Chemistry          |         11 |    56 | Failed
 Chemistry          |         17 |    60 | Passed
 Chemistry          |          5 |    61 | Passed
 Chemistry          |          8 |    61 | Passed
 Chemistry          |         13 |    62 | Passed
 Chemistry          |          6 |    63 | Passed
 Chemistry          |         16 |    67 | Passed
 Chemistry          |         14 |    70 | Passed
 Chemistry          |          4 |    71 | Passed
 Chemistry          |         18 |    71 | Passed
 Chemistry          |          2 |    73 | Passed
 Chemistry          |          7 |    74 | Passed
 Chemistry          |         19 |    75 | Passed
 Chemistry          |          9 |    81 | Passed
 Chemistry          |         24 |    82 | Passed
 Commerce           |         12 |    44 | Failed
 Commerce           |         22 |    55 | Failed
 Commerce           |         15 |    60 | Passed
 Commerce           |         11 |    61 | Passed
 Commerce           |         21 |    62 | Passed
. |            .  | . |    .
. |            .  | . |    .
. |            .  | . |    .

 
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:


```sql
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


 student_id |  subject   | score | status 
------------+------------+-------+--------
          1 | Statistics |    14 | Failed
         21 | Statistics |    48 | Failed
         22 | Statistics |    49 | Failed
         12 | Statistics |    52 | Failed
          6 | Statistics |    53 | Failed
         20 | Statistics |    54 | Failed
         15 | Statistics |    57 | Failed
         23 | Statistics |    57 | Failed
         13 | Statistics |    58 | Failed
          8 | Statistics |    60 | Passed
         14 | Statistics |    61 | Passed
         11 | Statistics |    62 | Passed
         18 | Statistics |    62 | Passed
         10 | Statistics |    65 | Passed
          5 | Statistics |    65 | Passed
         16 | Statistics |    66 | Passed
          4 | Statistics |    71 | Passed
         17 | Statistics |    76 | Passed
          7 | Statistics |    80 | Passed
         19 | Statistics |    80 | Passed
          9 | Statistics |    85 | Passed
         24 | Statistics |    88 | Passed
          2 | Statistics |    91 | Passed



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:

```sql
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
;
;
```


student_id |  subject   | score | grade 
------------+------------+-------+-------
          2 | Statistics |    91 | A
         24 | Statistics |    88 | B
          9 | Statistics |    85 | B
          7 | Statistics |    80 | B
         19 | Statistics |    80 | B
         17 | Statistics |    76 | C
          4 | Statistics |    71 | C
         16 | Statistics |    66 | D
         10 | Statistics |    65 | D
          5 | Statistics |    65 | D
         11 | Statistics |    62 | D
         18 | Statistics |    62 | D
         14 | Statistics |    61 | D
          8 | Statistics |    60 | D
         13 | Statistics |    58 | F
         15 | Statistics |    57 | F
         23 | Statistics |    57 | F
         20 | Statistics |    54 | F
          6 | Statistics |    53 | F
         12 | Statistics |    52 | F
         22 | Statistics |    49 | F
         21 | Statistics |    48 | F
          1 | Statistics |    14 | F



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:

```sql
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:


```sql

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;
```


      subject       | number_of_success | number_of_failure 
--------------------+-------------------+-------------------
 Arts               |                19 |                 4
 Biology            |                15 |                 8
 Chemistry          |                14 |                 9
 Commerce           |                20 |                 2
 Computer Science   |                19 |                 4
 Economics          |                22 |                 1
 English Language   |                16 |                 6
 History            |                21 |                 2
 Literature         |                21 |                 2
 Mathematics        |                21 |                 2
 Physical Education |                23 |                 0
 Physics            |                23 |                 0
 Statistics         |                14 |                 9





##### 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. 


```sql


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;


```


      subject       | number_of_success | number_of_failure 
--------------------+-------------------+-------------------
 Arts               |                19 |                 4
 Biology            |                15 |                 8
 Chemistry          |                14 |                 9
 Commerce           |                20 |                 2
 Computer Science   |                19 |                 4
 Economics          |                22 |                 1
 English Language   |                16 |                 6
 History            |                21 |                 2
 Literature         |                21 |                 2
 Mathematics        |                21 |                 2
 Physical Education |                23 |                 0
 Physics            |                23 |                 0
 Statistics         |                14 |                 9



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:


```sql
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;


```


      subject       |    percentage_passed     |    percentage_failed    
--------------------+--------------------------+-------------------------
 Arts               |  82.60869565217391304300 | 17.39130434782608695700
 Biology            |  65.21739130434782608700 | 34.78260869565217391300
 Chemistry          |  60.86956521739130434800 | 39.13043478260869565200
 Commerce           |  90.90909090909090909100 |  9.09090909090909090900
 Computer Science   |  82.60869565217391304300 | 17.39130434782608695700
 Economics          |  95.65217391304347826100 |  4.34782608695652173900
 English Language   |  72.72727272727272727300 | 27.27272727272727272700
 History            |  91.30434782608695652200 |  8.69565217391304347800
 Literature         |  91.30434782608695652200 |  8.69565217391304347800
 Mathematics        |  91.30434782608695652200 |  8.69565217391304347800
 Physical Education | 100.00000000000000000000 |  0.00000000000000000000
 Physics            | 100.00000000000000000000 |  0.00000000000000000000
 Statistics         |  60.86956521739130434800 | 39.13043478260869565200



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

```sql
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;

```

      subject       | percentage_passed | percentage_failed 
--------------------+-------------------+-------------------
 Arts               |             82.61 |             17.39
 Biology            |             65.22 |             34.78
 Chemistry          |             60.87 |             39.13
 Commerce           |             90.91 |              9.09
 Computer Science   |             82.61 |             17.39
 Economics          |             95.65 |              4.35
 English Language   |             72.73 |             27.27
 History            |             91.30 |              8.70
 Literature         |             91.30 |              8.70
 Mathematics        |             91.30 |              8.70
 Physical Education |            100.00 |              0.00
 Physics            |            100.00 |              0.00
 Statistics         |             60.87 |             39.13



##### 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.

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

WHERE subject = 'Chemistry'
ORDER BY score
;
```


 student_id |  subject  | score | status 
------------+-----------+-------+--------
          1 | Chemistry |     0 | Failed
         21 | Chemistry |     0 | Failed
         23 | Chemistry |    36 | Failed
         12 | Chemistry |    40 | Failed
         20 | Chemistry |    40 | Failed
         22 | Chemistry |    41 | Failed
         15 | Chemistry |    45 | Failed
         10 | Chemistry |    51 | Failed
         11 | Chemistry |    56 | Failed
         17 | Chemistry |    60 | Passed
          8 | Chemistry |    61 | Passed
          5 | Chemistry |    61 | Passed
         13 | Chemistry |    62 | Passed
          6 | Chemistry |    63 | Passed
         16 | Chemistry |    67 | Passed
         14 | Chemistry |    70 | Passed
         18 | Chemistry |    71 | Passed
          4 | Chemistry |    71 | Passed
          2 | Chemistry |    73 | Passed
          7 | Chemistry |    74 | Passed
         19 | Chemistry |    75 | Passed
          9 | Chemistry |    81 | Passed
         24 | Chemistry |    82 | Passed




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

 student_id |  score  
------------+-----------+---------------
         12 |     70  
         10 |     80 
         11 |     85 
         15 |     90 
     

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:

```sql

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:


```sql

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'

 student_id |  subject  | score | status 
------------+-----------+-------+--------
          1 | Chemistry |     0 | Failed
         21 | Chemistry |     0 | Failed
         23 | Chemistry |    36 | Failed
         20 | Chemistry |    40 | Failed
         22 | Chemistry |    41 | Failed
         17 | Chemistry |    60 | Passed
          8 | Chemistry |    61 | Passed
          5 | Chemistry |    61 | Passed
         13 | Chemistry |    62 | Passed
          6 | Chemistry |    63 | Passed
         16 | Chemistry |    67 | Passed
         14 | Chemistry |    70 | Passed
         12 | Chemistry |    70 | Passed
          4 | Chemistry |    71 | Passed
         18 | Chemistry |    71 | Passed
          2 | Chemistry |    73 | Passed
          7 | Chemistry |    74 | Passed
         19 | Chemistry |    75 | Passed
         10 | Chemistry |    80 | Passed
          9 | Chemistry |    81 | Passed
         24 | Chemistry |    82 | Passed
         11 | Chemistry |    85 | Passed
         15 | Chemistry |    90 | Passed


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 

```sql
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 

```sql
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:
```sql
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

![Dismissed img](https://media.giphy.com/media/3orieOPmJGswsJ7Ily/giphy.gif)