How to use a CASE statement in SQL
In SQL you can apply conditions on the cell based on other cells using CASE statement. In this article we will discuss how to use a case statement in sql in different scenarios.
Table of Content
Best-suited Data Analytics courses for you
Learn Data Analytics with these high-rated online courses
What is a SQL CASE statement?
SQL CASE statement is similar to the IF-THEN-ELSE statement of any other programming language that returns the result after evaluating conditions. If none of the conditions is evaluated to TRUE, it will return the condition given in the ELSE block.
Syntax
CASEWHEN condition_1 THEN value_1WHEN condition_2 THEN value_2……WHEN condition_n THEN value_nELSE valueEND
Points to Remember:
- CASE statement must be followed by at least one WHEN-THEN statement
- ELSE argument is optional
- NULL value can’t be checked using CASE statement
Now, let’s move to some examples to get a better understanding of how to use a CASE statement in SQL.
We will start with a simple example.
Must Read: Introduction to SQL
Must Read: SQL ACID Properties
Example -1: Below table (Student) contains the marks of ten students, using a case statement give each student a grade.
| Student ID | Name | Percentage |
| 1001 | Ajay | 87 |
| 1002 | Babloo | 81 |
| 1003 | Chhavi | 79 |
| 1004 | Dheeraj | 93 |
| 1005 | Evina | 95 |
| 1006 | Fredy | 80.7 |
| 1007 | Garima | 63 |
| 1008 | Hans | 49 |
| 1009 | Ivanka | 88 |
| 1010 | Jai | 74 |
Now, we will define grade for each students using CASE statement.
Query
SELECT * CASE WHEN Percentage > 90 THEN ‘O’ WHEN Percentage > 80 AND Percentage <= 90 THEN ‘A’ WHEN Percentage > 70 AND Percentage <= 80 THEN ‘B’ WHEN Percentage > 60 AND Percentage <= 70 THEN ‘C’ WHEN Percentage > 50 AND Percentage <= 60 THEN ‘D’ ELSE ‘Fail’ END AS Grade,FROM Student;
Output
| Student ID | Name | Percentage | Grade |
| 1001 | Ajay | 87 | A |
| 1002 | Babloo | 81 | A |
| 1003 | Chhavi | 79 | B |
| 1004 | Dheeraj | 93 | O |
| 1005 | Evina | 95 | O |
| 1006 | Fredy | 80.7 | A |
| 1007 | Garima | 63 | C |
| 1008 | Hans | 49 | F |
| 1009 | Ivanka | 88 | A |
| 1010 | Jai | 74 | B |
Must Read: Difference between SQL and NoSQL
CASE with GROUP BY
Example 2: Using the above Student data, classify the students in to Pass or Fail and count the number of student in both the category.
Query
SELECT CASE WHEN Percentage > 50 THEN ‘PASS’ ELSE ‘FAIL’ END AS Category, COUNT (*) AS Total _number_of_studentsFROM StudentGROUP BY Grade;
Output
| Category | Total _number_of_students |
| PASS | 9 |
| FAIL | 1 |
Must Read: Subqueries in SQL
Must Read: SQL CREATE TABLE
ORDER BY with CASE
Example 3: Sort the result of example 1 in Descending order i.e. sort the student marks such that highest grades are on top?
Query
SELECT Name CASE WHEN Percentage > 90 THEN ‘O’ WHEN Percentage > 80 AND Percentage <= 90 THEN ‘A’ WHEN Percentage > 70 AND Percentage <= 80 THEN ‘B’ WHEN Percentage > 60 AND Percentage <= 70 THEN ‘C’ WHEN Percentage > 50 AND Percentage <= 60 THEN ‘D’ ELSE ‘Fail’ END AS Grade,FROM StudentORDER BY Percentage DESC;
Output
| Name | Grade |
| Evina | O |
| Dheeraj | O |
| Ivanka | A |
| Ajay | A |
| Babloo | A |
| Fredy | A |
| Chhavi | B |
| Jai | B |
| Garima | C |
| Hans | F |
Now, in the next example we will use GROUP BY, ORDER BY and COUNT together
Must Read: SQL WHERE
Must Read: Introduction to Normalization
Example 4: Find the number of student in each grade.
Query
SELECT CASE WHEN Percentage > 90 THEN ‘O’ WHEN Percentage > 80 AND Percentage <= 90 THEN ‘A’ WHEN Percentage > 70 AND Percentage <= 80 THEN ‘B’ WHEN Percentage > 60 AND Percentage <= 70 THEN ‘C’ WHEN Percentage > 50 AND Percentage <= 60 THEN ‘D’ ELSE ‘Fail’ END AS Grade, COUNT(*) AS Total StudentFROM StudentGROUP BY GradeORDER BY Percentage DESC;
Output
| Grade | Total Student |
| O | 2 |
| A | 4 |
| B | 2 |
| C | 1 |
| D | 0 |
| F | 1 |
Must Read: SQL LIMITS
Must Check: SQL Online Course and Certifications
Conclusion
In this article, we have discussed how to use a CASE statement in SQL with the help of examples.
Hope this article, will help you in your Data Science/Data Analysis journey.
Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio
Top Picks & New Arrivals
