|
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_name |
Sales |
Date |
| Los Angeles |
$1500 |
Jan-05-1999 |
| San Diego |
$250 |
Jan-07-1999 |
| San Francisco |
$300 |
Jan-08-1999 |
| Boston |
$700 |
Jan-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_name |
New Sales |
Date |
| Los Angeles |
$3000 |
Jan-05-1999 |
| San Diego |
$375 |
Jan-07-1999 |
| San Francisco |
$300 |
Jan-08-1999 |
| Boston |
$700 |
Jan-08-1999 |
SQL NULL >>
Link to this page: If you find this page useful, we encourage you to link to this page. Simply copy and paste the code below to your website, blog, or profile.
Copyright 1999-2009 1keydata.com. All Rights Reserved. Privacy Policy
|
