Intermediate SQL and Basic CASE statements

You’ll learn how to build categorical variables, aggregate data into a single column with numerous filtering criteria, and calculate counts and percentages using the CASE WHEN statement.

Basic CASE statements

Select the title and the ID from the wp_posts table:

SELECT
	-- Select the title and the ID
	post_title,
	ID
FROM wp_posts
-- Only include these IDs
WHERE ID IN (21, 35);

Count the number of meta keys the posts have from the wp_postmeta table. The result will be three items.

-- Count the meta kyes each post has
SELECT 
    CASE WHEN post_id = 2322 THEN 'Basic CASE statements'
         WHEN post_id = 2280 THEN 'Introduction to Git'
         ELSE 'Other' END AS title,
	COUNT(post_id) AS total_matches
FROM wp_postmeta
-- Group by the CASE statement alias
GROUP BY title;

CASE statements comparing column values

Select the dates when a user visited the site before and after a certain date.

SELECT 
	-- Select the time when the user visited the site
	viewed,
	CASE WHEN viewed > '2022-03-26 23:29:53' THEN 'After 26 of March'
        WHEN viewed < '2022-03-26 23:29:53' THEN 'Before 26 of March' 
        ELSE 'March 26' END AS visit
FROM wp_user_activity_info ORDER BY viewed DESC;

You can join more tables. The result will be an array of two columns. The first one will show the country code, and the second one will show if that user visited before a certain date or not.

SELECT 
	m.country_code,
	-- Complete the CASE statement with an alias
	CASE WHEN viewed > '2022-03-26 23:29:53' THEN 'After 26 of March'
        WHEN viewed < '2022-03-26 23:29:53' THEN 'Before 26 of March' 
        ELSE 'March 26' END AS visit
FROM wp_user_activity AS m
LEFT JOIN wp_user_activity_info AS t
ON m.ID = t.activity_id ORDER BY m.country_code ASC

Limit the search by column. The result will be three columns. Country code, post, and when the post was viewed.

SELECT 
	m.country_code, t.post,
	-- Complete the CASE statement with an alias
	CASE WHEN viewed > '2022-03-26 23:29:53' THEN 'After 26 of March'
        WHEN viewed < '2022-03-26 23:29:53' THEN 'Before 26 of March' 
        ELSE 'March 26' END AS visit
FROM wp_user_activity AS m
LEFT JOIN wp_user_activity_info AS t
ON m.ID = t.activity_id WHERE t.taxonomy= 'post_tag' ORDER BY m.country_code ASC;

It’s important to analyze which rows of your data are part of your ELSE clause and if they’re appropriately categorized when evaluating logical conditions.

You may use AND within your WHEN clause to test several logical criteria in a CASE statement.