SQL Case Cheat Sheet in Common Concepts

CASE Clause

SQL provides CASE clause to perform conditional operations. This is similar to the switch case / if-else conditions in other programming languages.

Let's learn more about the usage of CASE clause using the given database

Database The IMDb dataset which consists of movies, actors and cast. You can refer to the database in the code playground for a better understanding.

CASE Clause

Each condition in the

CASE

clause is evaluated and results in corresponding value when the first condition is met.

Syntax

SELECT c1, c2
CASE
    WHEN condition1 THEN value1
    WHEN condition2 THEN value2
    ...
    ELSE value
    END AS cn
FROM table;

Note

1. In CASE clause, if no condition is satisfied, it returns the value in the ELSE part. If we do not specify the ELSE part,

   CASE clause
   results in NULL

2. We can use CASE in various clauses like SELECT, WHERE, HAVING, ORDER BY and GROUP BY.

Example

Calculate the tax amount for all movies based on the profit. Check the following table for tax percentages.

profit	tax_percentage
<=100 crores	10% of profit
100< <=500 crores	15% of profit
> 500 crores	18% of profit

SELECT id, name,
  CASE
    WHEN collection_in_cr - budget_in_cr <= 100 THEN collection_in_cr - budget_in_cr * 0.1
    WHEN (collection_in_cr - budget_in_cr > 100
    AND collection_in_cr - budget_in_cr < 500) THEN collection_in_cr - budget_in_cr * 0.15
    ELSE collection_in_cr - budget_in_cr * 0.18
  END AS tax_amount
FROM
  movie;

Output

id	name	tax_amount
1	The Matrix	45.8
2	Inception	82.08
3	The Dark Knight	98.7
...	...	...

Try it Yourself

Question 1

Categorise movies as following.

rating	category
< 5	Poor
5 <= _ <= 7	Average
7 <	Good

CASE with Aggregates

CASE statements can also be used together with aggregate functions

Example

• Get the number of movies with rating greater than or equal to 8, and the movies with rating less than 8, and are released between 2015 and 2020.

SELECT
  count(
    CASE
      WHEN rating >= 8 THEN 1
    END
  ) AS above_eight,
  count(
    CASE
      WHEN rating < 8 THEN 1
    END
  ) AS below_eight
FROM
  movie
WHERE
  CAST(strftime("%Y", release_date) AS INTEGER) BETWEEN 2015
  AND 2020;

Output

above_eight	below_eight
4	2

Try it Yourself!

• Get the number of movies with collection greater than or equal to 100 crores, and the movies with collection less than 100 crores.

Output

above_100_cr	below_100_cr
13	7