SQL Part 8

In the last part of the previous blog, I introduced the CASE statement which is capable of creating a new column and filling it with a value based on whether a condition is met or not. Let us further examine the CASE statement and what else it is capable of.

Multiple conditions CASE statement

It is possible to write several CASE statements in one SQL query. Let us use the movie dataset that was introduced in the previous blog post again. This time let's set several ranges for the total movie gross and categorize them.

SELECT Title,
   distributor,
   “world_sales_(in_$)”
   CASE WHEN “world_sales_(in_$)” > 2500000000 THEN ‘Over 2.5B’
   WHEN “world_sales_(in_$)” > 2000000000 THEN ‘Over 2B less than 
          2.5B’
   WHEN “world_sales_(in_$)” > 1000000000 THEN ‘Over 1B less than 
          2B’
   ELSE ‘Less than 1B’ END AS gross_range
    FROM HighestGrossing

The query above creates a column that can contain four different category ranges for the total world gross of each movie. It checks whether the gross is over two and a half billion and then check if the gross is between two billion and two and a half billion and so on.

In the above example, the WHEN/THEN statements are evaluated in the order that they are written in. It checks whether the value is over two and a half billion first and if it is, the given value is used to fill the column, and the other WHEN statements aren’t evaluated anymore. Had the world_sales > 2,000,000,000 been written first, a movie with a gross of 2.8 billion would have been incorrectly categorized as ‘Over 2B less than 2.5B’.

Therefore the order you write each WHEN statement is extremely important. A much better way to write each statement would be to include two different numerical values in each WHEN statement. For example, the above statement should be written as:

SELECT Title,
   distributor,
   “world_sales_(in_$)”,
   CASE WHEN “world_sales_(in_$)” > 2500000000 THEN ‘Over 2.5B’
        WHEN “world_sales_(in_$)” > 2000000000 AND    
             “world_sales_(in_$)” < 2500000000 THEN ‘Over 2B less  
              than 2.5B’
        WHEN “world_sales_(in_$)” > 1000000000 AND 
             “world_sales_(in_$)” < 2000000000 THEN ‘Over 1B less 
              than 2B’
   ELSE ‘Less than 1B’ END AS gross_range
   FROM HighestGrossing

With the above query, the order of the WHEN statement no longer matters. You can also see that AND and OR can be used along with the CASE WHEN statements to string together several conditionals.

USING CASE STATEMENTS WITH AGGREGATE FUNCTIONS

You can use CASE statements along with aggregate functions to count the movies with worldwide gross of over 2.5 billion using the statement below:

SELECT CASE WHEN “world_sales_(in_$)” > 2500000000 THEN ‘Over 2.5B’
   ELSE ‘Less than 2.5B’ END AS gross_range,
   COUNT(1) AS COUNT
   FROM HighestGrossing
   GROUP BY 1

Although you might be thinking could the same count be done using a WHERE clause which would look like:

SELECT COUNT(1) AS ‘over_2.5B’
   FROM HighestGrossing
   WHERE “world_sales_(in_$)” > 2500000000

If you wanted to count multiple categories you need to use a CASE statement. This method will be further explored in the next blog.