SQL CASE



  SQL > Advanced SQL > Case

CASE is used to provide if-then-else type of logic to SQL. Its syntax is:

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

"condition" can be a static value or an expression. The ELSE clause is optional.

In our Table Store_Information example,

Table Store_Information
store_nameSalesDate
Los Angeles$1500Jan-05-1999
San Diego$250Jan-07-1999
San Francisco$300Jan-08-1999
Boston$700Jan-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, we key in,

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

"New Sales" is the name given to the column with the CASE statement.

Result:

store_nameNew SalesDate
Los Angeles$3000Jan-05-1999
San Diego$375Jan-07-1999
San Francisco$300Jan-08-1999
Boston$700Jan-08-1999


Next: SQL DECODE




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


SQL UNION
SQL UNION ALL
SQL Inline View
SQL INTERSECT
SQL MINUS
SQL LIMIT
SQL TOP
SQL Subquery
SQL EXISTS
SQL CASE
SQL DECODE
SQL AUTO INCREMENT
SQL IDENTITY
SQL SEQUENCE And NEXTVAL
SQL NULL
SQL ISNULL
SQL IFNULL
SQL NVL
SQL COALESCE
SQL NULLIF
SQL Rank
SQL Median
SQL Running Totals
SQL Percent to Total
SQL Cumulative Percent to Total


SQL Video Tutorial
SQL Jobs




Site Map
Resources