SQL > Advanced SQL > CaseCASE is used to provide if-then-else type of logic to SQL. There are two formats: The first is aSimple CASE expression, where we compare an expression to static values. The second is aSearched CASE expression, where we compare an expression to one or more logical conditions.Simple CASE ExpressionThe syntax for a simple CASE expression is:
SELECT CASE ("column_name")
The ELSE clause is optional.WHEN "value1" THEN "result1" WHEN "value2" THEN "result2" ... [ELSE "resultN"] END FROM "table_name"; For example, assume we have the following Store_Information table, Table Store_Information
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
"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.WHEN 'Los Angeles' THEN Sales * 2 WHEN 'San Diego' THEN Sales * 1.5 ELSE Sales END "New Sales", Txn_Date FROM Store_Information; Result:
Searched CASE ExpressionThe syntax for a searched CASE expression is:
SELECT CASE
The ELSE clause is optional. "Condition" can consist of one or more logical statements.WHEN "condition1" THEN "result1" WHEN "condition2" THEN "result2" ... [ELSE "resultN"] END FROM "table_name"; If we want to define the status of a store's sale based on the following rules:
SELECT Store_Name, Txn_Date, CASE
Result:WHEN Sales >= 1000 THEN 'Good Day' WHEN Sales >= 500 THEN 'OK Day' ELSE 'Bad Day' END "Sales Status" FROM Store_Information;
Simple CASE Expression:
SELECT Store_Name, CASE Store_Name
Searched CASE Expression:WHEN 'Los Angeles' THEN Sales * 2 WHEN 'San Diego' THEN Sales * 1.5 ELSE Sales END "New Sales", Txn_Date FROM Store_Information;
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; |
Saturday, 13 December 2014
SQL CASE statement
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment