Understanding the SQL Server CASE statement
Understanding the SQL Server CASE statement
View all posts by Ben Richardson Latest posts by Ben Richardson (see all) Working with the SQL MIN function in SQL Server - May 12, 2022 SQL percentage calculation examples in SQL Server - January 19, 2022 Working with Power BI report themes - February 25, 2021
SQLShack
SQL Server training EspañolUnderstanding the SQL Server CASE statement
June 28, 2019 by Ben Richardson SQL Server CASE statement is equivalent to the IF-THEN statement in Excel. The CASE statement is used to implement the logic where you want to set the value of one column depending upon the values in other columns. The SQL Server CASE Statement consists of at least one pair of WHEN and THEN statements. The WHEN statement specifies the condition to be tested. The THEN statement specifies the action if the WHEN condition returns TRUE. The ELSE statement is optional and executes when none of the WHEN conditions return true. The CASE statement ends with an END keyword. In this article, we will take a look at a number of different examples of the CASE statement. But before we do that, we’ll create some dummy data to work with.Creating dummy data
Execute the following script to create the dummy data: 1234567891011121314 CREATE Database ShowRoom;GOUSE ShowRoom; CREATE TABLE Cars( id INT, name VARCHAR(50) NOT NULL, company VARCHAR(50) NOT NULL, power INT NOT NULL, color VARCHAR(50) NOT NULL, model INT NOT NULL, condition VARCHAR(50) NOT NULL ) The script above has created a dummy database called ShowRoom with one Table in it called Cars. The Cars table has seven columns: id, name, company, power, color, model, and condition. Now let’s insert some dummy data into the Cars table. Execute the following script: 123456789101112131415 USE ShowRoom INSERT INTO Cars VALUES(1, 'Corrolla', 'Toyota', 1800, 'red', 1995, 'X'),(2, 'City', 'Honda', 1500 , 'black', 2015, 'X'),(3, 'C200', 'Mercedez', 2000 , 'white', 1992, 'X'),(4, 'Vitz', 'Toyota', 1300 , 'blue', 2007, 'X'),(5, 'Baleno', 'Suzuki', 1500 , 'white', 2012, 'X'),(6, 'C500', 'Mercedez', 5000 , 'grey', 1994, 'X'),(7, '800', 'BMW', 8000 , 'blue', 2016, 'X'),(8, 'Mustang', 'Ford', 5000 , 'red', 1997, 'X'),(9, '208', 'Peugeot', 5400, 'black', 1999, 'X'),(10, 'Prius', 'Toyota', 3200 , 'red', 2003, 'X') Let’s check how our dataset looks, execute the following script: 1 SELECT * FROM Cars The output looks like this: You can see that the condition column contains an X in each row at the moment. We will set the value of the condition column, depending on the model column, using the CASE statement so that you can see clearly what is going on.SQL Server CASE statement syntax
The syntax of the CASE statement is pretty straight forward: 12345 SELECT column1, column2, CASE WHEN CONDITION THEN 'Value1' ELSE 'Value2' END AS columnX FROM Cars The CASE statement has to be included inside the SELECT Statement. It starts with the CASE keyword followed by the WHEN keyword and then the CONDITION. The condition can be any valid SQL Server expression which returns a boolean value. For instance, the condition can be model > 2000, the THEN clause is used after the CONDITION. If the CONDITION returns true the value that follows the THEN clause is stored in columnX. Else, the value after the ELSE clause, will also be stored in columnX. The SQL Server CASE statement ends with the END clause.CASE statement examples
Let’s now see the CASE statement in action. In a previous section, we created a table named Cars inside the ShowRoom database. The condition column had the value X for all rows. We will use the SQL Server CASE statement to set the value of the condition column to “New” if the model column has a value greater than 2000, otherwise the value for the condition column will be set to “Old”. Look at the following script: 12345 SELECT name, model, CASE WHEN model > 2000 THEN 'New' ELSE 'Old' END AS condition FROM Cars The above script displays the name, model and condition columns from the Cars table. The output of the script above looks like this: You can see that the value of X in the condition column has been replaced by “New” and “Old” depending upon the model of the car.Multiple conditions in CASE statement
You can evaluate multiple conditions in the CASE statement. Let’s write a SQL Server CASE statement which sets the value of the condition column to “New” if the value in the model column is greater than 2010, to ‘Average’ if the value in the model column is greater than 2000, and to ‘Old’ if the value in the model column is greater than 1990. Look at the following script: 1234567 SELECT name, model, CASE WHEN model > 2010 THEN 'New' WHEN model > 2000 THEN 'Average' WHEN model > 1990 THEN 'Old' ELSE 'Old' END AS condition FROM Cars The output of the script above looks like this: In the script above, we assigned three different values to the condition column depending on the value in the model column. However, in the above script, the conditions are overlapping as. the model with a value greater than 2010 also has a value greater than 2000 and 1990. A better way to implement multiple conditions is to use logical operators like AND, OR, NOT, etc. Look at the following script: 1234567 SELECT name, model, CASE WHEN model > 2010 THEN 'New' WHEN model > 2000 AND model <2010 THEN 'Average' WHEN model > 1990 AND model <2000 THEN 'Old' ELSE 'Old' END AS condition FROM Cars The output of the script above looks like this: We can also evaluate multiple conditions from different columns using the SQL Server CASE statement. In the following example, we will assign the value of “New White” to the condition column where the model is greater than 2010 and the color is white. Look at the following script: 123456789 SELECT name, color, model, CASE WHEN model > 2010 AND color = 'white' THEN 'New White' WHEN model > 2010 THEN 'New' WHEN model > 2000 AND model <2010 THEN 'Average' WHEN model > 1990 AND model <2000 THEN 'Old' ELSE 'Old' END AS condition FROM Cars The output looks like this: You can see from row 5 that since the color is white and the model is greater than 2010, value for the condition column has been set to “New White” in the output.Using GROUP BY with SQL Server CASE statement
The CASE statement can also be used in conjunction with the GROUP BY statement in order to apply aggregate functions. For example, if we want to count the number of new (model number greater than 2000) and old (model number less than 2000) vehicles, we can use the GROUP BY clause with the CASE statement as follows: 1234567 SELECT CASE WHEN model > 2000 THEN 'New' ELSE 'Old' END AS condition, COUNT(1) AS count FROM Cars GROUP BY CASE WHEN model > 2000 THEN 'New' ELSE 'Old' END In the script above we use the COUNT aggregate function with the CASE statement. The SQL Server CASE statement sets the value of the condition column to “New” or “Old”. Inside the GROUP BY clause, we specify that the corresponding count for “New” is incremented by 1, whenever a model value of greater than 2000 is encountered. The Else section means that we increase the count for “Old” by 1 if the value of the model is 2000 or less. The output of the script above looks like this: Since our dataset has five vehicles with a value for ‘model’ of greater than 2000, you can see a 5 in the count column for “New” vehicles. Similarly, we had 5 old vehicles and hence we can see 5 for the count column of “Old” vehicles. Similarly, we can GROUP BY more than two values. Look at the following script: 1234567891011 SELECT CASE WHEN model > 2010 THEN 'New' WHEN model > 2000 THEN 'Average' WHEN model > 1990 THEN 'Old' ELSE 'Old' END AS condition, COUNT(1) AS count FROM Cars GROUP BY CASE WHEN model > 2010 THEN 'New' WHEN model > 2000 THEN 'Average' WHEN model > 1990 THEN 'Old' ELSE 'Old' END In the script above, we grouped the data into three categories: “New”, “Average” and “Old”. The output looks like this: You can see the count for “New”, “Average” and “Old” condition cars.Conclusion
The CASE statement comes in handy when you want to implement IF-THEN logic in SQL Server. In this article, we saw what CASE statement is along with its syntax. We also saw different examples of CASE statement along with its usage with the GROUP BY clause.Other great articles from Ben
Understanding SQL Server query plan cache What is the difference between Clustered and Non-Clustered Indexes in SQL Server? How to use window functions Querying data using SQL Server CASE statement Author Recent Posts Ben RichardsonBen Richardson runs Acuity Training a leading provider of SQL training the UK. It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity’s blogView all posts by Ben Richardson Latest posts by Ben Richardson (see all) Working with the SQL MIN function in SQL Server - May 12, 2022 SQL percentage calculation examples in SQL Server - January 19, 2022 Working with Power BI report themes - February 25, 2021