SQL CASE conditional expression

Link: SQL

SELECT + CASE §

Usage §

create new columns, categorization

SELECT
  column_1,
  column_2,
  CASE
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ELSE result_3
  END AS column_3_name -- Give your new column an alias
FROM table_1;

Example §

-- Create a new column to add a "Popular" tag if follower >= 500k
SELECT
  character,
  platform,
  CASE
    WHEN followers >= 500000 THEN 'Popular'
  END AS is_popular
FROM marvel_avengers;

WHERE + CASE §

Usage: §

filter rows based on conditions

SELECT
  column_1,
  column_2
FROM table_1
WHERE CASE
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ELSE result_3
  END;

Example §

 
-- For Twitter, filtering actors with 200,000 or more followers.
-- For other platforms, filtering actors with 100,000 or more followers.
SELECT
  actor,
  character,
  platform
FROM marvel_avengers
WHERE
  CASE
    WHEN platform = 'Instagram' THEN followers >= 500000 -- For Instagram, filtering actors with 500,000 or more followers.
    WHEN platform = 'Twitter' THEN followers >= 200000
    ELSE followers >= 100000
  END;

CASE + Aggregated functions §

Combine with SQL aggregate functions such as COUNT() to count based on conditions.

CASE + COUNT() §

Example:

-- Assign to popular_actor_count if number of actors with followers greater than or equal to 500,000 followers.
-- Assign to less_popular_actor_count if number of actors with followers less than 500,000 followers.
SELECT
  platform,
  COUNT(CASE
    WHEN followers >= 500000 THEN 1
    ELSE NULL
  END) AS popular_actor_count,
  COUNT(CASE
    WHEN followers < 500000 THEN 1
    ELSE NULL
  END) AS less_popular_actor_count
FROM marvel_avengers
GROUP BY platform;

Result:

platform	popular_count	less_popular_count
Instagram	2	0
Twitter	0	2
YouTube	1	0

CASE + SUM() §

-- If the engagement rate is 8.0 or higher, add the followers in the "high_engagement_followers_sum".
-- Otherwise, add them in the "low_engagement_followers_sum".
SELECT
  platform,
  SUM(CASE
    WHEN engagement_rate >= 8.0 THEN followers
    ELSE 0
  END) AS high_engagement_followers_sum,
  SUM(CASE
    WHEN engagement_rate < 8.0 THEN followers
    ELSE 0
  END) AS low_engagement_followers_sum
FROM marvel_avengers
GROUP BY platform;

Result:

platform	high_engagement_followers_sum	low_engagement_followers_sum
Instagram	1200000	0
Twitter	0	500000
YouTube	400000	0

CASE + AVG() §

• Calculate averages based on certain conditions
• The usage is similar to SUM()

SELECT
  platform,
  AVG(CASE
    WHEN engagement_rate >= 8.0 THEN followers
    ELSE NULL
  END) AS avg_high_engagement_followers,
  AVG(CASE
    WHEN engagement_rate < 8.0 THEN followers
    ELSE NULL
  END) AS avg_low_engagement_followers
FROM marvel_avengers
GROUP BY platform;