---
title: "What is the purpose of the CASE statement in SQL, and how is it used?"  
description: "What is the purpose of the CASE statement in SQL, and how is it used?"  
author: "Steilla Mitchel"  
published: 2023-09-04  
updated: 2023-09-26  
canonical: https://www.mindstick.com/forum/159817/what-is-the-purpose-of-the-case-statement-in-sql-and-how-is-it-used  
category: "mssql server"  
tags: ["sql server", "sql"]  
reading_time: 3 minutes  

---

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

What is the [purpose](https://yourviews.mindstick.com/view/247/no-fail-policy-failing-its-purpose) of the CASE [statement in SQL](https://www.mindstick.com/forum/33706/what-is-the-difference-between-having-clause-and-group-by-statement-in-sql-server), and how is it used?

## Replies

### Reply by Aryan Kumar

The **CASE** statement in [SQL](https://www.mindstick.com/articles/13115/types-of-keys-in-sql-or-oracle-database) 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:

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

```plaintext
SELECT product_name,
       CASE
           WHEN price < 10 THEN 'Low Price'
           WHEN price >= 10 AND price <= 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:

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

```plaintext
SELECT product_name, price
FROM products
ORDER BY
    CASE
        WHEN price < 10 THEN 1
        WHEN price >= 10 AND price <= 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:

```plaintext
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.


---

Original Source: https://www.mindstick.com/forum/159817/what-is-the-purpose-of-the-case-statement-in-sql-and-how-is-it-used

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.