Determining the Result Set

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)