How to Write a CASE Statement in MySQL

The `CASE` statement in MySQL allows you to implement conditional logic within your SQL queries. It functions similarly to an `IF-THEN-ELSE` statement in programming. You can use it to return specific values based on conditions, making your queries more dynamic and versatile.

Syntax

SELECT 
    CASE 
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE result3
    END
FROM table_name;

In this syntax:

• WHEN specifies a condition to test.
• THEN indicates the result to return if the condition is true.
• ELSE defines the default result if none of the conditions are true.

Example 1: Simple CASE Statement

This example shows how to use the `CASE` statement to return specific categories based on a score field:

SELECT student_name,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        ELSE 'D'
    END AS grade
FROM students;

In this query, the `CASE` statement evaluates the `score` column and returns a corresponding letter grade.

Example 2: Using CASE with Aggregate Functions

Here's an example where the `CASE` statement is used inside an aggregate function to summarize sales data by categories:

SELECT 
    category,
    SUM(CASE 
            WHEN sales > 1000 THEN 1
            ELSE 0
        END) AS high_sales_count
FROM products
GROUP BY category;

This query counts the number of products in each category that have sales greater than 1000.

Conclusion

The `CASE` statement is a powerful tool that can simplify complex conditional logic directly in your SQL queries. Whether you're working with simple comparisons or combining it with aggregate functions, the `CASE` statement enhances your ability to create dynamic and responsive queries in MySQL.