Use case expression to test for conditions that determine the result set.
The syntax is:
case when search_condition1 then result1 when search_condition2 then result2 . . . when search_conditionn then resultn else resultx end
where search_condition is a logical expression, and result is an expression.
If search_condition1 is true, the value of case is result1; if search_condition1 is not true, search_condition2 is checked. If search_condition2 is true, the value of case is result2, and so on. If none of the search conditions are true, the value of case is resultx. The else clause is optional. If it is not used, the default is else NULL. end indicates the end of the case expression.
The total sales of each book for each store are kept in the salesdetail table. To show a series of ranges for the book sales, you can track how each book sold at each store:
Books that sold less than 1000 (low-selling books)
Books that sold between 1000 and 3000 (medium-selling books)
Books that sold more than 3000 (high-selling books)
Write the following query:
select stor_id, title_id, qty, "Book Sales Catagory" = case when qty < 1000 then "Low Sales Book" when qty >= 1000 and qty <= 3000 then "Medium Sales Book" when qty > 3000 then "High Sales Book" end from salesdetail group by title_id
stor_id title_id qty Book Sales Catagory ------- -------- ---- ------------------ 5023 BU1032 200 Low Sales Book 5023 BU1032 1000 Low Sales Book 7131 BU1032 200 Low Sales Book . . . 7896 TC7777 75 Low Sales Book 7131 TC7777 80 Low Sales Book 5023 TC7777 1000 Low Sales Book 7066 TC7777 350 Low Sales Book 5023 TC7777 1500 Medium Sales Book 5023 TC7777 1090 Medium Sales Book (116 rows affected)
The following example selects the titles from the titleauthor table according to the author’s royalty percentage (royaltyer) and then assigns each title with a value of high, medium, or low royalty:
select title, royaltyper, "Royalty Category" = case when (select avg(royaltyper) from titleauthor tta where t.title_id = tta.title_id) > 60 then "High Royalty" when (select avg(royaltyper) from titleauthor tta where t.title_id = tta.title_id) between 41 and 59 then "Medium Royalty" else "Low Royalty" end from titles t, titleauthor ta where ta.title_id = t.title_id order by title
title royaltyper royalty Category ------- ---------- ---------------- But Is It User Friendly? 100 High Royalty Computer Phobic and Non-Phobic Ind 25 Medium Royalty Computer Phobic and Non-Phobic Ind 75 Medium Royalty Cooking with Computers: Surreptiti 40 Medium Royalty Cooking with Computers: Surreptiti 60 Medium Royalty Emotional Security: A New Algorith 100 High Royalty . . . Sushi, Anyone? 40 Low Royalty The Busy Executive’s Database Guide 40 Medium Royalty The Busy Executive’s Database Guide 60 Medium Royalty The Gourmet Microwave 75 Medium Royalty You Can Combat Computer Stress! 100 High Royalty (25 rows affected)
