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

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.

For example, assume we have the following Store_Information table,

Table Store_Information

Store_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
San Francisco300Jan-08-1999
Boston700Jan-08-1999

if we want 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_NameNew SalesTxn_Date
Los Angeles3000Jan-05-1999
San Diego375Jan-07-1999
San Francisco300Jan-08-1999
Boston700Jan-08-1999

Searched CASE Expression

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.

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_NameTxn_DateSales Status
    Los AngelesJan-05-1999Good Day
    San DiegoJan-07-1999Bad Day
    San FranciscoJan-08-1999Bad Day
    BostonJan-08-1999OK 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 DECODE




    Copyright © 2014 1keydata.com   All Rights Reserved.   Privacy Policy