Projects Engine
  • Home
  • About
  • Gutenberg
  • Blog
  • Invoice
  • Contact
  • Login
  • Home
  • About
  • Gutenberg
  • Blog
  • Invoice
  • Contact
  • Login
Intermediate SQL and Basic CASE statements

Intermediate SQL and Basic CASE statements

Posted on March 27, 2022 | 3 minutes read | case statements, sql

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;
Intermediate SQL and Basic CASE statements

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;
Intermediate SQL and Basic CASE statements

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.

Was this article helpful?
Yes No
Share Tweet Share

Post navigation

Previous postIntroduction to Git
Next postPersonal Effectiveness Tips: Ensure you’re viewed as credible

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Projects Engine

Projects Engine provides code and tutorials for your themes and plugins you need to create remarkable sites on WordPress that will drive your business forward faster. The primary purpose of this site is to give high-quality WordPress tips, tricks, hacks, and other tools to help WordPress developers improve their site(s) and skills. Projects Engine was founded in December 2020 by Dragi Postolovski.

submit your tutorial

Subscribe

  • Home
  • Privacy Policy
  • Terms and Conditions
  • Disclaimer
  • Dictionary
  • Plugins
  • Contact

Copyright 2022 Projects Engine