SQL is an essential part of database management and analysis. The SQL CASE WHEN statement is a powerful tool to perform conditional logic within a query. In this tutorial, you will learn how to use SQL CASE WHEN AS to transform data based on specific conditions.
Understanding SQL CASE WHEN AS
The SQL CASE WHEN statement is used to perform conditional operations in SQL. It allows you to transform data based on conditions specified in the query. SQL CASE WHEN AS is an extension of the SQL CASE WHEN statement which allows you to assign aliases to the resulting columns.
SQL CASE WHEN AS has the following syntax:
SELECT column1, column2, CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END AS alias_name FROM table_name
In this syntax,
column2 are the columns selected from the
table_name. Inside the
CASE statement, you can specify multiple
WHEN clauses followed by a
THEN clause. If none of the conditions are true, the
ELSE clause specifies the default result. The
AS keyword is used to assign an alias to the resulting column.
Here are some examples to help you understand how to use SQL CASE WHEN AS.
Example 1: Assigning Grades
Consider a table
students that contains the following columns:
SELECT name, score, CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' WHEN score >= 70 THEN 'C' WHEN score >= 60 THEN 'D' ELSE 'F' END AS grade FROM students
In this example, we use SQL CASE WHEN AS to assign grades based on the score. If the score is greater than or equal to 90, the grade is ‘A’. If the score is between 80 and 89, the grade is ‘B’, and so on. The resulting output will have three columns:
Example 2: Categorizing Expenses
Consider a table
expenses that contains the following columns:
SELECT category, SUM(amount) AS total_amount, CASE WHEN SUM(amount) > 1000 THEN 'High' WHEN SUM(amount) > 500 THEN 'Medium' ELSE 'Low' END AS expense_category FROM expenses GROUP BY category
In this example, we use SQL CASE WHEN AS to categorize expenses based on the total amount spent. If the total amount spent is greater than 1000, the expense category is ‘High’. If the total amount spent is between 500 and 999, the expense category is ‘Medium’, and so on. The
GROUP BY clause is used to group the expenses by category, and the
SUM() function is used to calculate the total amount spent in each category. The resulting output will have three columns:
- Make sure to use the correct syntax for SQL CASE WHEN AS.
- Double-check your conditions and results to ensure they are accurate.
- Use aliases that are meaningful and easy to understand.
SQL CASE WHEN AS is a powerful tool to perform conditional operations in SQL. It allows you to transform data based on specific conditions and assign aliases to the resulting columns. By following the examples and tips in this tutorial, you can use SQL CASE WHEN AS to transform and categorize data in your own SQL queries.