How to Write a Case Statement

In Redshift, the CASE statement allows you to apply conditional logic directly within SQL queries, providing a powerful way to transform data, filter rows, or create new computed columns based on specific conditions.

What is a CASE Statement?

A CASE statement is similar to an IF-THEN-ELSE logic. It evaluates each condition in sequence and returns the corresponding result for the first condition that is true. It’s useful for categorizing data based on predefined conditions.

Basic Syntax

SELECT column_name,
       CASE 
           WHEN condition_1 THEN result_1
           WHEN condition_2 THEN result_2
           ELSE default_result
       END as new_column
FROM table_name;
                                

Example 1: Categorizing Sales

Suppose you have a table of sales data and you want to categorize the sales performance as 'Good', 'Average', or 'Poor' based on the amount of sales. Here's how you would write the CASE statement:

SELECT salesperson,
       sales_amount,
       CASE
           WHEN sales_amount > 10000 THEN 'Good'
           WHEN sales_amount BETWEEN 5000 AND 10000 THEN 'Average'
           ELSE 'Poor'
       END as performance
FROM sales_data;
                                

Example 2: Conditional Aggregation

Another common use case for the CASE statement is performing conditional aggregation. For instance, you might want to calculate the total sales for 'Good' performers:

SELECT salesperson,
       SUM(CASE WHEN sales_amount > 10000 THEN sales_amount ELSE 0 END) as total_good_sales
FROM sales_data
GROUP BY salesperson;
                                

Important Notes

• CASE statements are evaluated in the order they are written. Once a condition is true, subsequent conditions are not checked.
• Use the ELSE clause to define a default value when no conditions are met.
• The CASE statement can be used in SELECT, WHERE, ORDER BY, and HAVING clauses.

Conclusion

The CASE statement is an essential tool in Redshift for implementing logic directly in SQL queries. Whether you’re categorizing data, creating new calculated columns, or filtering rows based on specific criteria, the CASE statement is versatile and powerful for your data manipulation needs.