Manipulating Data With the SQL CASE Statement

Whether you’re assigning conditional values, performing conditional aggregation, implementing complex sorting, or handling conditional joins, the CASE statement proves to be an indispensable asset in your SQL toolkit.

There are many ways to build SQL competency so we have decided to take a different approach. For the next few days, you will be serving as the newly hired Data Analyst for Basira and Co, An Afro fusion restaurant with a network of chains across Africa and you’ll be reporting to the Chief Growth Officer (CGO) Mrs Ahmadu.

Everyday, you’ll get analytical requests/tasks from her related to the SQL topic of the day. The goal for this is to make each lesson as practical and as relatable as possible.

HOW TO PRACTICE: Here is a link to our practice guide. We have already prepared the table schema in advance so all you need to do is plug that into DBFiddle.com and you are all set! The LinkedIn forum is a great way to share any comments or questions about the practice guide.

YOU’VE GOT MAIL!

Good Morning!
I’d like you to help categorize the performance of each restaurant based on its total revenue. Can you to categorize them into two groups. ‘Good’ for restaurants generating above $1000 and ‘Low’ for restaurants generating below or equal to $1000.
Best Regards,
Mrs Ahmadu (CGO)

SQL TOPIC OF THE DAY

The CASE statement is a conditional expression in SQL that allows you to perform different actions based on various conditions. It is similar to a switch statement in programming languages, providing a way to handle multiple scenarios within a single query.
The basic syntax of the SQL CASE statement is as follows

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    …
    ELSE result_else
END

The ELSE clause is optional and provides a default result if none of the specified conditions are met.

Common Use Cases for the CASE statement:

1. Conditional Aggregation

In scenarios where you want to perform conditional aggregation, the CASE statement proves to be very handy. Consider a sales table with columns for product_id and quantity_sold. You want to calculate the total quantity sold for each product, but for products with a quantity sold less than 10, you want to label them as Low Sales. Here’s how you can achieve this:

SELECT product_id, SUM(quantity_sold) as total_quantity_sold,
    CASE
        WHEN SUM(quantity_sold) < 10 THEN ‘Low Sales’
        ELSE ‘Normal Sales’
    END as sales_category
FROM sales
GROUP BY product_id;

This query returns a list of distinct colors present in the color column of the product_colors table.

2. Complex Sorting

The CASE statement is also useful when you need to perform sorting based on multiple conditions. Consider an employees table with columns for first_name, last_name, and hire_date. You want to sort the employees based on their last names, but for employees hired in the last year, you want them to appear first. Here’s an example:

SELECT first_name, last_name, hire_date
FROM employees
ORDER BY
    CASE
        WHEN hire_date >= ‘2023-01-01’ THEN 1
        ELSE 2
    END,
    last_name;

This query sorts the employees first based on the condition of being hired in the last year (assigned a value of 1), and then by their last names.

3. Conditional Joins

The CASE statement can be applied in JOIN conditions to handle different types of matches. Suppose you have a customers table with a column for preferred_status, and you want to join it with a sales table. You want to retrieve all sales for preferred customers and only successful sales for non-preferred customers. Here’s how you can achieve this:

SELECT s.order_id, s.customer_id, s.total_amount
FROM sales s
JOIN customers c ON s.customer_id = c.customer_id
WHERE
    CASE
        WHEN c.preferred_status = ‘Preferred’ THEN 1
        WHEN c.preferred_status != ‘Preferred’ AND s.payment_status = ‘Success’ THEN 1
        ELSE 0
    END = 1;

This query uses the CASE statement in the WHERE clause to conditionally filter the results based on the preferred status of customers and the payment status of sales.

SQL PRACTICE

Now, let’s write a script to answer the question of the day.
Mrs Ahmadu would like you to categorize the performance of each restaurant based on it’s total revenue into two. ‘Good’ for restaurants generating above $1000 and ‘Low’ for restaurants generating below or equal to $1000

SELECT r.name, SUM(o.price_paid) as revenue,
   CASE
        WHEN SUM(o.price_paid) > 1000 THEN ‘Good’
        WHEN SUM(o.price_paid) >= 1000 THEN ‘Low’
    END as performance
FROM restaurants r
LEFT JOIN orders o ON r.id = o.restaurant_id
GROUP BY 1;

FEEDBACK FROM THE CGO

Thanks for the quick turn around as usual. Seems the results are skewed to one group, can you create an extra group called ‘Above Average’ for the restaurants generating above average revenue?
Warm regards,
Mrs Ahmadu (CGO

With what we learned today, think you can solve this part on your own?
Did you learn anything new? Share your new insights with us on Facebook, LinkedIn or Instagram and don’t forget to tag us!