SQL > Advanced SQL > Case

CASE is used to provide if-then-else type of logic to SQL. There are two formats: The first is a Simple CASE expression, where we compare an expression to static values. The second is a Searched CASE expression, where we compare an expression to one or more logical conditions.

Simple CASE Expression Syntax

The syntax for a simple CASE expression is:

SELECT CASE ("column_name")
  WHEN "value1" THEN "result1"
  WHEN "value2" THEN "result2"
  ...
  [ELSE "resultN"]
  END
FROM "table_name";

The ELSE clause is optional.

Simple CASE Expression Example

We use the following table for our example.

Table Store_Information

 Store_Name  Sales  Txn_Date 
 Los Angeles 1500  Jan-05-1999 
 San Diego 250  Jan-07-1999 
 San Francisco 300  Jan-08-1999 
 Boston 700  Jan-08-1999 

To multiply the sales amount from 'Los Angeles' by 2 and the sales amount from 'San Diego' by 1.5 while keeping the sales amount for other stores the same, we would use the following SQL statement using CASE:

SELECT Store_Name, CASE Store_Name
  WHEN 'Los Angeles' THEN Sales * 2
  WHEN 'San Diego' THEN Sales * 1.5
  ELSE Sales
  END
"New Sales",
Txn_Date
FROM Store_Information;

"New Sales" is the name given to the column with the CASE statement. This is an example of a simple CASE expression, because the conditions listed, 'Los Angeles' and 'San Diego', are static values.

Result:

Store_Name New Sales Txn_Date
Los Angeles 3000 Jan-05-1999
San Diego 375 Jan-07-1999
San Francisco 300 Jan-08-1999
Boston 700 Jan-08-1999

Searched CASE Expression Syntax

The syntax for a searched CASE expression is:

SELECT CASE
  WHEN "condition1" THEN "result1"
  WHEN "condition2" THEN "result2"
  ...
  [ELSE "resultN"]
  END
FROM "table_name";

The ELSE clause is optional. "Condition" can consist of one or more logical statements.

Searched CASE Expression Example

We use the same Store_Information above. If we want to define the status of a store's sale based on the following rules:

  • If Sales >= 1,000, it's a "Good Day"
  • If Sales >= 500 and < 1,000, it's an "OK Day"
  • If Sales < 500, it's a "Bad Day"

    We can use the following searched CASE expression:

    SELECT Store_Name, Txn_Date, CASE
      WHEN Sales >= 1000 THEN 'Good Day'
      WHEN Sales >= 500 THEN 'OK Day'
      ELSE 'Bad Day'
      END
    "Sales Status"
    FROM Store_Information;

    Result:

    Store_Name Txn_Date Sales Status
    Los Angeles Jan-05-1999 Good Day
    San Diego Jan-07-1999 Bad Day
    San Francisco Jan-08-1999 Bad Day
    Boston Jan-08-1999 OK Day

    Note that a simple CASE expression is a special case of a searched CASE expression. As an example, the following two CASE expressions are identical:

    Simple CASE Expression:

    SELECT Store_Name, CASE Store_Name
      WHEN 'Los Angeles' THEN Sales * 2
      WHEN 'San Diego' THEN Sales * 1.5
      ELSE Sales
      END
    "New Sales",
    Txn_Date
    FROM Store_Information;

    Searched CASE Expression:

    SELECT Store_Name, CASE
      WHEN Store_Name = 'Los Angeles' THEN Sales * 2
      WHEN Store_Name = 'San Diego' THEN Sales * 1.5
      ELSE Sales
      END
    "New Sales",
    Txn_Date
    FROM Store_Information;


    Next: SQL Pivot

    This page was last updated on October 11, 2024.



    Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact
    		•