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**

**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:** What is the difference between SQL and MySQL?

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

Download this article as PDF to read offline

Download as PDF**About the Author**

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

## Comments