SQL CASE Tutorial With Examples

The statement in SQL allows you to shape, transform, manipulate, and filter data based on specified conditions. It's a conditional expression tool that lets you customize query results, create new categories, and apply conditional logic.

Here's what we'll be covering:

• Using statement in statement
• Using statement in clause
• Counting results using SQL in statement
• Adding results using SQL in statement
• Averaging results using SQL in statement

Using CASE Statement in SELECT Statement

The statement in the statement is used to create new columns, categorize data, or perform calculations based on specified conditions. It helps tailor the output of your query to meet specific requirements.

The statement evaluates the specified conditions sequentially and returns a value when it encounters the first true condition. If none of the conditions are met, the clause provides a default value.

Using CASE Statement in WHERE Clause

The statement in the clause is used to filter rows based on specified conditions within the dataset.

The statement evaluates conditions for each row, determining whether it meets the filtering criteria. Rows satisfying the conditions specified in the statement are included in the result.

In this tutorial, we'll explore different ways of using the statement with the dataset. This dataset contains information about Marvel Avengers actors and their associated social media presence.

Example:

Assigning Categories with CASE Statement in SELECT Statement

Here's an example that determines the popularity category of each Marvel character based on their number of followers ("Popular" if they have 500,000 or more followers).

Here's the output:

Handling Multiple Conditions with CASE Statement in SELECT Statement

We can create groups using multiple conditions in the statement. For example, we can categorize the Marvel actors based on their social media engagement rates as follows:

• High Engagement: Engagement rate greater than or equal to 8.0
• Moderate Engagement: Engagement rate between 6.0 and 7.9 (inclusive)
• Low Engagement: Engagement rate less than 6.0

Result

Using CASE-ELSE Clause with CASE Statement in SELECT Statement

When none of the previous conditions match, we can use the clause to provide a default value.

For the Marvel Avengers characters, let's classify their into three categories based on their number of followers:

• For characters with 700,000 or more followers, label them as "Highly Popular."
• For characters with followers between 300,000 and 699,999, label them as "Moderately Popular."
• For characters with fewer than 300,000 followers, label them as "Less Popular."

Result

Now, it's your turn to practice writing a SQL query in an exercise which uses and statements.

Analyze the marvel actors dataset, and categorize them based on the average number of likes:

• "Super Likes" for characters with an average of 15,000 or more likes.
• "Good Likes" for characters with an average between 5,000 and 14,999 likes (inclusive).
• "Low Likes" for characters with an average of fewer than 5,000 likes.

Filtering Conditions with CASE Statement in WHERE Clause

Suppose we want to filter the dataset based on the social media platforms, but we want to include an option to filter based on different criteria for each platform. We'll use the statement in the clause to achieve this.

• For Instagram, we're filtering actors with 500,000 or more followers.
• For Twitter, we're filtering actors with 200,000 or more followers.
• For other platforms, we're filtering actors with 100,000 or more followers.

Here's the output:

Counting Results using SQL COUNT() in CASE Statement

The aggregate function within a statement is used to count occurrences based on various conditions within the dataset. If you need a refresher, check out our SQL Aggregate Functions tutorial with COUNT(), SUM() and AVG()!

Assume we're given the following conditions:

• Assign to if number of actors with followers greater than or equal to 500,000 followers.
• Assign to if number of actors with followers less than 500,000 followers.

Results shown:

Adding Results using SQL SUM() in CASE Statement

The aggregate function within a statement allows us to add values based on specific conditions within the dataset, providing a cumulative value.

In the example below, we're adding up the followers of actors based on their engagement rates grouped by platform:

• 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".

Here's the results:

NYT SQL Interview Question: Laptop vs. Mobile Viewership

Let's use the statement, along with function to solve a real SQL Interview Question from NYTimes. In this SQL assesment, you're given a table on user viewership categorized by device type where the three types are laptop, tablet, and phone.

Example Input:

Write a query that calculates the total viewership for laptops and mobile devices where mobile is defined as the sum of tablet and phone viewership. Output the total viewership for laptops as and the total viewership for mobile devices as .

Example Output:

Based on the example output, can you give the question a go?

Averaging results using SQL AVG() in CASE statement

The aggregate function within a statement calculates averages based on specific conditions within the dataset, providing an average value.

Let's say we're calculating the average number of followers of actors based on their engagement rates. If the engagement rate is 8.0 or higher, assign to "high_engagement_followers", otherwise, assign to "low_engagement_followers" for each platform.

Output shown:

What's Next: JOINS

Up until now, we've focused on analyzing a single table of data at a time. Whether it be the , , or datasets, we've only been applying all these fancy arithmetic operators, hunting down nulls, and applying to data that come from a single table.

It's time to change it up, and start analyzing data from TWO tables using the keyword!

Next Tutorial: SQL Joins