CASE

CASE is a general conditional expression that allows you to test data for equality with a specified value, partition data into ranges, or perform any other conditional check against the data. It is similar to an if-then-else statement in standard programming languages. The CASE expression is comprised of the keywords CASE, WHEN, THEN, ELSE, and END. CASE, WHEN, THEN, and END are required, ELSE is optional and if omitted will return a null value if none of the conditions are met. The data is tested in order it’s presented in the query, and the first value that meets the conditions will determine the result of the CASE expression. It is good practice always include the ELSE keyword to clarify your intent. There are two forms of the CASE expression: The simple form and the general form.

Simple CASE Expression

The simple CASE expression can be used to test data for equality or other numeric comparisons and has the column against which the test is run listed after the CASE keyword.

If you wanted to classify sales agents by coastal or central regions you could run the following query:

SELECT sales_agent,
       CASE regional_office
         WHEN  "East" THEN "Coast"
         WHEN  "West" THEN "Coast"
         WHEN  "Central" THEN "Central"
         ELSE "Unknown"
       END AS region
  FROM sales_teams

The first several rows of the resulting table would look like this:

If you wanted to label deals according to a size you could run the query:

SELECT DISTINCT sales_agent,
       CASE close_value
         WHEN > 30000 THEN "1"
         WHEN > 20000 THEN "2"
         WHEN > 10000 THEN "3"
         WHEN > 5000 THEN "4"
         WHEN > 1000 THEN "5"
         WHEN > 500 THEN "6"
         WHEN > 250 THEN "7"
         ELSE "8"
       END AS `sales tier`
  FROM sales_pipeline
 WHERE deal_stage = "Won"

The first several rows of the resulting table would look like:

CASE can also be used in an ORDER BY clause to sort your results. The same query sorted would be:

SELECT DISTINCT sales_agent,
       CASE close_value
         WHEN > 30000 THEN "1"
         WHEN > 20000 THEN "2"
         WHEN > 10000 THEN "3"
         WHEN > 5000 THEN "4"
         WHEN > 1000 THEN "5"
         WHEN > 500 THEN "6"
         WHEN > 250 THEN "7"
         ELSE "8"
       END AS `sales tier`
  FROM sales_pipeline
 WHERE deal_stage = "Won"
 ORDER BY `sales tier`

The first several rows of the results would look like:

General CASE Expression

The general CASE expression is more powerful than the simple CASE expression and can be used in all the instances where the simple CASE expression can be used. Additionally it can contain arbitrary boolean expressions including AND, OR, and NOT. In the general CASE expression, the column names are located after the WHEN keyword.

We could write a query similar to the one above to show the ranking of sales agents into sales tiers using a range of values:

SELECT DISTINCT sales_agent,
       CASE
         WHEN close_value > 0 AND close_value < 250 THEN "bronze"
         WHEN close_value >= 250 AND close_value < 500 THEN "silver"
         WHEN close_value >= 500 AND close_value < 1000 THEN "gold"
         WHEN close_value >= 1000 AND close_value < 5000 THEN "sapphire"
         WHEN close_value >= 5000 AND close_value < 10000 THEN "ruby"
         WHEN close_value >= 10000 AND close_value < 20000 THEN "emerald"
         WHEN close_value >= 20000 AND close_value < 30000 THEN "diamond"
         WHEN close_value > 30000 THEN "platinum"
         ELSE NULL
       END AS `sales tier`
  FROM sales_pipeline
 ORDER BY close_value DESC

The initial rows of the resulting table would be:

Exercises (Continued from previous section)

Exercise 30

Write a query against the observations_cleaned table using the general CASE expression to return the patient and description columns, the average of the value column, and unit column along with a new column labeled BMI category. The results are only for the records where the description is Body Mass Index. The entries in the BMI category column should be Underweight for values under 18.5, Healthy for values from 18.5 to 24.9, Overweight for values of 25-29.9, and Obese for values over 30.

Try query