Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

Home Forums What is the purpose of the CASE statement in SQL, and how is it used? – MindStick
Ask Question

1 Answers

Aryan Kumar
Aryan Kumar 26 Sep 2023
Report Abuse

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

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

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.

advertising
Markdown for AI

A clean, structured version of this page for AI assistants and LLMs.

Open .md