What is the purpose of the CASE statement in SQL, and how is it used?

Question

Aryan Kumar · Answer

The CASE statement in SQL is a conditional expression that allows you to perform conditional logic within your SQL queries. It serves several purposes, including data transformation, conditional aggregation, and custom result value assignment based on specified conditions. You can use the CASE statement in both
SELECT statements and other SQL clauses like WHERE,
ORDER BY, and HAVING. Here's how it works and some common use cases:
Basic Syntax:
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE else_result
END
CASE begins the statement.WHEN condition THEN result specifies the conditions and their corresponding results.You can have multiple WHEN clauses.ELSE provides a default result if none of the conditions are met.END ends the CASE statement.
Use Cases:
Data Transformation: You can use the CASE statement to transform data based on specific conditions. For example, you might want to categorize products into different price ranges:
SELECT product_name,
       CASE
           WHEN price andlt; 10 THEN 'Low Price'
           WHEN price >= 10 AND price andlt;= 50 THEN 'Medium Price'
           ELSE 'High Price'
       END AS price_category
FROM products;
Conditional Aggregation: The CASE statement is handy for performing conditional aggregations. You can use it within aggregate functions like
SUM, COUNT, or AVG to calculate values based on specific conditions:
SELECT department,
       SUM(CASE WHEN sales_amount > 1000 THEN 1 ELSE 0 END) AS high_sales_count
FROM sales
GROUP BY department;
This query counts the number of sales with amounts greater than 1000 in each department.
Custom Sorting: You can use the CASE statement in the
ORDER BY clause to create custom sorting logic:
SELECT product_name, price
FROM products
ORDER BY
    CASE
        WHEN price andlt; 10 THEN 1
        WHEN price >= 10 AND price andlt;= 50 THEN 2
        ELSE 3
    END;
This query orders products by price range.
Conditional Filtering: You can use CASE in the
WHERE clause to filter rows based on specific conditions:
SELECT order_id, order_date
FROM orders
WHERE
    CASE
        WHEN order_status = 'Pending' THEN 1
        WHEN order_status = 'Processing' THEN 2
        ELSE 0
    END >= 2;
This query retrieves orders that are either "Processing" or "Pending."
The CASE statement is a powerful tool in SQL that allows you to add flexibility and logic to your queries, making it possible to handle complex data transformations and conditional operations. It helps you customize your query results based on various conditions, making SQL queries more versatile.

forum

What is the purpose of the CASE statement in SQL, and how is it used?

Steilla Mitchel

Can you answer this question?

1 Answers

Basic Syntax:

Use Cases:

Liked By