SQL Server CASE expression is very useful in returning an output based on some conditional matching criteria. We can state multiple conditions in the CASE..WHEN expression and get the associated output defined in the THEN or ELSE statement. CASE expression offers two approaches to compare and return a condition-based result.
- Simple CASE expression
- Search CASE expression
As per MSDN, the Simple CASE expression is a straightforward statement that compares an expression with multiple simple expressions defined under the WHEN clause whereas the Search CASE expression compares a set of boolean expressions to return the result.
Have a look at the syntax of both types of CASE expressions. Make sure to change texts and expressions given under <> and in italic.
--Simple CASE Expression. Change expressions mentioned in <> with your desired expressions
CASE <Input Expression>
WHEN <When expression> THEN <Then output expression>
ELSE <Else expression>
END
--Search CASE Expression. Change expressions mentioned in <> with your desired expressions
CASE
WHEN <Boolean expression> THEN <output expression>
ELSE <Else output expression>
ENDThe CASE expression is very flexible and can be used with any statement like SELECT, UPDATE, SET, etc., or with any clause like WHERE, ORDER BY, GROUP BY, HAVING, etc. We can use multiple SQL Server CASE expressions together in one script. I will show each one in this article.
Before proceeding, let’s identify the table on which we are going to demonstrate our use cases. I will use a table dbo.DimProduct in a database AdventureWorksDW2019 which I installed using my previous article for the below use cases. Run the below SELECT statement to list the details of this table.
SELECT [EnglishProductName]
,[Color]
,[ListPrice]
,[Size]
,[DaysToManufacture]
,[Status]
FROM [AdventureWorksDW2019].[dbo].[DimProduct]You can see the output of this table in the below screenshot to understand the kind of data stored here.
Let’s assume one scenario for which I will fetch the data using the CASE expressions. Our company has multiple products listed in their inventory as shown in the above image and now, the company wants to run a discount-based promotional campaign to attract more buyers. You have been tasked to identify potential buyers for this program so that your sales team can directly approach them to sell the identified products based on the applied discount for each respective product. I will show you how useful CASE expression will be in identifying products from inventory based on the identified discounts and potential buyers to whom the company can sell their discounted products. First, I will use a table DimProduct to identify the products with applied discounts and a DimCustomer table to find potential buyers.
Searching the CASE Expression with Multiple WHEN Conditions
The first part of this promotional campaign is to identify the products which will go with this discount campaign. The company has decided to put discounts based on the listed price of products as given below.
- A buyer will get a 15% discount if the product price is up to $30
- A buyer will get a 20% discount if the product price is between $30.01 to $70
- A buyer will get a 25% discount if the product price is between $70.01 to $120
- A buyer will get a 30% discount if the product price is between $120.01 to $400
- A buyer will get a 35% discount if the product price is more than $400
Based on these conditions we will apply the discount coupons. SQL Server CASE WHEN expression will help you identify all products and return the output as per action items defined in the THEN or ELSE statement.
SELECT [EnglishProductName]
,[Color]
,[ListPrice]
,[Size]
,[DaysToManufacture]
,[Status],
CASE
WHEN [ListPrice]<=30.00 THEN 'Apply 15% Discount'
WHEN [ListPrice] BETWEEN 30.01 AND 70.00 THEN 'Apply 20% Discount'
WHEN [ListPrice] BETWEEN 70.01 AND 120.00 THEN 'Apply 25% Discount'
WHEN [ListPrice] BETWEEN 120.01 AND 400.00 THEN 'Apply 30% Discount'
WHEN [ListPrice] >400 THEN 'Apply 35% Discount'
ELSE 'No Price Mentioned. NULL value'
END AS [Discount Promotions]
FROM [AdventureWorksDW2019].[dbo].[DimProduct]Once you execute the above T-SQL, you will get the below output with a column [Discount Promotions] mentioned in the END statement of the above query. This column will display all the action items defined in the THEN or ELSE statement based on the condition stated with the WHEN statement. Just focus on two columns one is listprice and another one is discount promotions and here you can compare the conditions and their output w.r.t applied the discount coupon.
We can also consider this T-SQL statement as a use case or demonstration for the CASE expression to be used with the SQL BETWEEN operator.
You can get a similar output and identify the products for their respective conditional discount coupons by simply using other operators like = or > or < but I would not suggest using them because of one rule to define these conditions of listprice in the increasing order. If you rearrange any expressions defined in the WHEN statement within the CASE expression, your output will be impacted. Here, I will verify this by showing you an example.
You can see I have removed the BETWEEN operator from the above T-SQL script and replaced it with equal to, greater than, or less than operators in the below T-SQL statement. You can run this script to get a result.
SELECT [EnglishProductName]
,[Color]
,[ListPrice]
,[Size]
,[Status],
CASE
WHEN [ListPrice]<=30 THEN 'Apply 15% Discount'
WHEN [ListPrice] <= 70 THEN 'Apply 20% Discount'
WHEN [ListPrice] <= 120 THEN 'Apply 25% Discount'
WHEN [ListPrice] <= 400 THEN 'Apply 30% Discount'
WHEN [ListPrice] >=401 THEN 'Apply 35% Discount'
ELSE 'No Price Mentioned. NULL value'
END AS [Discount Promotions]
FROM [AdventureWorksDW2019].[dbo].[DimProduct]Again, you have the correct result in the below output, and this can be verified by comparing the two columns.
You can see all conditions for listprice column described with a WHEN statement is in the increasing order in the above T-SQL script. The first listprice condition is for up to $30 products, then for $70 products, and so on. Now, I will rearrange the first statement for listprice of products up to $30 and put it as the last expression within the CASE statement. You can see this in the below T-SQL statement. Run the below statements to get an output.
SELECT [EnglishProductName]
,[Color]
,[ListPrice]
,[Size]
,[Status],
CASE
WHEN [ListPrice] <= 70 THEN 'Apply 20% Discount'
WHEN [ListPrice] <= 120 THEN 'Apply 25% Discount'
WHEN [ListPrice] <= 400 THEN 'Apply 30% Discount'
WHEN [ListPrice] >400 THEN 'Apply 35% Discount'
WHEN [ListPrice]<=30 THEN 'Apply 15% Discount'
ELSE 'No Price Mentioned. NULL value'
END AS [Discount Promotions]
FROM [AdventureWorksDW2019].[dbo].[DimProduct]Let’s compare it now as we did in the above outputs. You can see row number 218, 219, 223, and many other rows where a 15% discount coupon should apply because their prices are less than $30. However, because of this rearrangement of the WHEN statement, it is shown as a 20% discount. It means the CASE expression is reading, matching, and returning the output sequentially while using these operators. Your job might be at risk if you will not use this T-SQL statement correctly because your action will impact the company revenue by giving more than expected discounts.
Discover more about the efficient use of the AS statement in SQL subqueries in our in-depth article.
CASE Expression with the SUM Function
SUM is a T-SQL function that returns the sum of all values specified in expressions. I will show you how to use the CASE expression with the SQL SUM function.
Suppose you want to know about the total number of products in each discount category. I will show you an example based on the same table DimProduct for our demonstration and here I will list a number of products that qualify under each discounted coupon with the help of function SUM and CASE expression. Have a look at the below SELECT statement, I have used a CASE expression inside the SUM function.
This is also a use case to learn how to use CASE expression for NULL values. Look at the last SUM function in the below T-SQL statements where I have used a CASE expression and a function SUM for product prices which are listed as a NULL value.
--USE SUM function with CASE expression
SELECT
SUM(
CASE
WHEN [ListPrice]<=30.00 THEN 1
ELSE 0
END) As [15% Discount],
SUM(CASE
WHEN [ListPrice] BETWEEN 30.01 AND 70.00 THEN 1 ELSE 0 END) As [20% Discount],
SUM(CASE
WHEN [ListPrice] BETWEEN 70.01 AND 120.00 THEN 1 ELSE 0 END) As [25% Discount],
SUM(CASE
WHEN [ListPrice] BETWEEN 120.01 AND 400.00 THEN 1 ELSE 0 END) As [30% Discount],
SUM (CASE
WHEN [ListPrice] >400 THEN 1 ELSE 0 END) As [35% Discount],
SUM(CASE WHEN [ListPrice] IS NULL THEN 1 ELSE 0 END) As [PRICE Not Mentioned on Product]
FROM [AdventureWorksDW2019].[dbo].[DimProduct]You can change the above T-SQL statement as per your requirements.
Here, we can see the total number of products falls under each discount coupon or category. We can see that there are 211 products listed without any price and their prices are registered as NULL. You can get the list of those products and work out on relisting its prices for this campaign.
Using the CASE Expression with COUNT and GROUP BY
I have used a SUM function in the above section to list the total number of products qualified for each discount. We can get the same output or report using the COUNT function as well. I will show you its demonstration in this section. You can use this example as another use case of the CASE expression on how to use it with a COUNT function and a GROUP BY statement. This is a different way to get the same output we have fetched in the above example.
COUNT function returns the number of rows that matched the specific criteria. I have used the COUNT function with a CASE expression in the below example and then used the CASE expression in the SQL GROUP BY statement to group the rows based on their conditions and display the result. The first CASE statements in the below script will find or label each product based on their listing price and condition mentioned in the CASE..WHEN clause. Then the COUNT function will count all these rows for their respective discount category and finally the GROUP BY statement will group all rows that have the same value or label and display the result in its output.
SELECT [All Discounts] =
CASE
WHEN [ListPrice]<=30.00 THEN 'Apply 15% Discount'
WHEN [ListPrice] BETWEEN 30.01 AND 70.00 THEN 'Apply 20% Discount'
WHEN [ListPrice] BETWEEN 70.01 AND 120.00 THEN 'Apply 25% Discount'
WHEN [ListPrice] BETWEEN 120.01 AND 400.00 THEN 'Apply 30% Discount'
WHEN [ListPrice] >400 THEN 'Apply 35% Discount'
ELSE 'No Price Mentioned. NULL value'
END,
COUNT(ISNULL(ListPrice,0)) AS [ProductCount]
FROM [AdventureWorksDW2019].[dbo].[DimProduct]
GROUP BY CASE
WHEN [ListPrice]<=30.00 THEN 'Apply 15% Discount'
WHEN [ListPrice] BETWEEN 30.01 AND 70.00 THEN 'Apply 20% Discount'
WHEN [ListPrice] BETWEEN 70.01 AND 120.00 THEN 'Apply 25% Discount'
WHEN [ListPrice] BETWEEN 120.01 AND 400.00 THEN 'Apply 30% Discount'
WHEN [ListPrice] >400 THEN 'Apply 35% Discount'
ELSE 'No Price Mentioned. NULL value'
ENDWe can see its output in the below screenshot, and it is the same as in our previous use case. This is also one example where our objective was to get the total number of products to fall under each discount category.
SQL CASE Expression with the WHEN Statement Having Multiple Conditions
Let’s take this example on the same table and in the same sequence of events. Here, I will show you how to put multiple conditions in the CASE WHEN clause. Let’s say, your company has decided to put more discounts on some specific types of products which are left in the inventory because they are not included in the sales. The company has decided to give a 50% discount on products which has:
- Listing price more than $400
- Weight more than 10 KG
- Color is RED
There could be more conditions based on specific requirements. First, you need to identify all those products which are meeting the above criteria. We will use a CASE expression with multiple conditions in the WHEN clause to filter the result to apply a 50% discount. Have a look at the CASE WHEN statement of the below SELECT query.
SELECT [EnglishProductName]
,[Color]
,[ListPrice]
,[Weight]
,[Size],
CASE
WHEN [ListPrice] >400 AND [Weight] >10 AND [Color] ='Red' THEN 'Apply 50% Discount'
ELSE 'No Discount'
END AS [Top Discounts]
FROM [AdventureWorksDW2019].[dbo].[DimProduct]We can verify whether all our conditions are met for products which have a 50% discount. Look at row number 326 to 331 in the below image where a 50% discount has been applied. All products are in RED, their weights are more than 10KG and their price is also more than $400.
The CASE expression is very useful in such reporting requirements. Now, let’s go ahead and take this example to next level with another CASE expression use case. Let’s say, you want to find the number of products where a 50% discount has been applied. Here we will use a SUM function with the CASE expression to get the output.
SELECT
SUM(
CASE
WHEN [ListPrice] >400 AND [Weight] >10 AND [Color] ='Red' THEN 1
ELSE 0
END) As [Top Discounts]
FROM [AdventureWorksDW2019].[dbo].[DimProduct]
Go
SELECT [EnglishProductName],[ListPrice],[Weight],[Color]
FROM [AdventureWorksDW2019].[dbo].[DimProduct]
WHERE [ListPrice] >400 AND [Weight] >10 AND [Color] ='Red'I have shown two sets of T-SQL statements in the above script. The first set of scripts will return the total sum of the products which have a 50% discount, and this was returned using the function SUM () and a CASE expression. Another T-SQL statement is a validation script to reverify whether our output using the CASE expression is correct. You can compare and match the output; it will be 100% correct.
Multiple CASE WHEN Expressions
We have successfully identified all products with discounts. Now next objective was to identify potential customers whom companies’ sales teams can target to sell the products. Here, we will use another table DimCustomer to find customer details. This example will also show another use case of the CASE expression. Here, I will show you how to use multiple CASE WHEN expressions in one SELECT statement.
The company has decided to target married couples to sell the products as most of the products belong to the baby products category. The first CASE expression will be used to filter potential buyers who are married, and the second CASE expression will be used to define categories within potential buyers. The category will be defined based on their yearly income. Those who are earning more than $80000 per year will be categorized as “highest”, more than $60000 per year will be categorized as “aggressive”, more than $40000 per year will be categorized as “major” and those who are earning more than $20000 per year and less than $40000 per year will be categories as “can buy”. The sales team will define their approach for each respective potential customer based on these categories.
SELECT [FirstName]
,[LastName]
,[MaritalStatus]
,[YearlyIncome]
,[TotalChildren],
CASE
WHEN [MaritalStatus] = 'M' THEN 'Married, Can be a Buyer'
ELSE 'Single. Not Interested'
END AS PotentialBuyers,
CASE
WHEN [MaritalStatus] = 'M' AND [YearlyIncome] >=80000 THEN 'Highest'
WHEN [MaritalStatus] = 'M' AND [YearlyIncome] >=60000 THEN 'Aggressive'
WHEN [MaritalStatus] = 'M' AND [YearlyIncome] >=40000 THEN 'Major'
WHEN [MaritalStatus] = 'M' AND [YearlyIncome] >=20000 THEN 'Can Buy'
ELSE 'No Target'
END AS [Target approach]
FROM [AdventureWorksDW2019].[dbo].[DimCustomer]The above script has two CASE expressions. The first CASE expression will filter out buyers based on the condition whether they are married or not. The next CASE expression is helping sales to define their sales approach for various potential buyers based on their yearly income.
This section has demonstrated how to use multiple CASE expressions in a single query to fulfill your business needs. Go ahead and use it as per your need.
Using the CASE Expression with a WHERE Clause
This section will show you a use case of the CASE expression with a WHERE clause. I will use the same example as above and apply a WHERE clause in the above output. We have fetched the list of potential buyers based on their marital status and yearly income. But as I stated above, most of the products fall under baby products or baby toys, so the company has decided to first target only those married couples who have kids and a yearly income of more than $20000 per year.
SELECT [FirstName]
,[LastName]
,[MaritalStatus]
,[YearlyIncome]
,[TotalChildren],
CASE
WHEN [MaritalStatus] = 'M' THEN 'Married, Can be a Buyer'
ELSE 'Single. Not Interested'
END AS PotentialBuyers,
CASE
WHEN [MaritalStatus] = 'M' AND [YearlyIncome] >=80000 THEN 'Highest'
WHEN [MaritalStatus] = 'M' AND [YearlyIncome] >=60000 THEN 'Aggressive'
WHEN [MaritalStatus] = 'M' AND [YearlyIncome] >=40000 THEN 'Major'
WHEN [MaritalStatus] = 'M' AND [YearlyIncome] >=20000 THEN 'Can Buy'
ELSE 'No Target'
END AS [Target approach]
FROM [AdventureWorksDW2019].[dbo].[DimCustomer]
WHERE [MaritalStatus]='M' AND [TotalChildren]>0 AND [YearlyIncome] >=20000Now, we have only 7837 potential buyers based on this filtering in the below image whereas total potential buyers were close to 19000 in the above section. This way a company can focus on only targeted customers first.
Use CASE Expression with ORDER BY Clause
Another example of the CASE expression is using it with the ORDER BY clause. In this section, I have listed in descending order all customers with their respective yearly incomes who are married and have at least one child. We can get a similar output by using a CASE expression in the ORDER BY statement as I have used in the below T-SQL script.
SELECT [FirstName]
,[LastName]
,[MaritalStatus]
,[YearlyIncome]
,[TotalChildren]
FROM [AdventureWorksDW2019].[dbo].[DimCustomer]
ORDER BY
CASE WHEN [MaritalStatus] ='M' AND [TotalChildren]>0 THEN [YearlyIncome] END DESCYou can see the output in the below image.
Below is another example where I have used CASE expressions with the ORDER BY and the WHERE clause both together.
SELECT [FirstName]
,[LastName]
,[MaritalStatus]
,[YearlyIncome]
,[TotalChildren]
FROM [AdventureWorksDW2019].[dbo].[DimCustomer]
WHERE [TotalChildren]>0 AND [MaritalStatus] ='M'
ORDER BY
CASE [MaritalStatus] WHEN 'M' THEN [YearlyIncome] END DESCAgain, the WHERE clause has applied additional filtering to display the result.
These are various representations of the data. You are free to use distinct clauses as per your business needs.
CASE Expression in UPDATE Statement
This section will show you how to use a CASE expression in an UPDATE statement. First, let’s create a table and fill in some data inside that table. I have created a new table “NewCustomer” in a database AdventureworksDW209.
USE [AdventureWorksDW2019]
GO
CREATE TABLE [dbo].[NewCustomer](
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[NameStyle] [bit] NULL,
[BirthDate] [date] NULL,
[MaritalStatus] [nchar](1) NULL,
[Suffix] [nvarchar](10) NULL,
[Gender] [nvarchar](1) NULL,
[EmailAddress] [nvarchar](50) NULL,
[YearlyIncome] [money] NULL,
[TotalChildren] [tinyint] NULL,
[NumberChildrenAtHome] [tinyint] NULL,
[Status] [nvarchar](15)NULL)Here, you can see the details of the rows I have inserted in this table. You can see the values in the column Status are NULL. Now we will update this column in this section with the help of the CASE expression in the UPDATE statement.
Now, I must run the UPDATE statement on this table to update the status details for each customer. The status column should be updated based on each customer’s yearly income and marital status so that next time a company can target them for any campaign. I will use the CASE expression in this UPDATE statement to show you an example. Here you can see I have updated a newly created table and set the column status value as “Target customer” for those who meet our conditions and “NA” for those who do not meet our conditions.
UPDATE [AdventureWorksDW2019].[dbo].[NewCustomer]
SET [Status]=(CASE WHEN [MaritalStatus]='M' AND [YearlyIncome] >200000 THEN 'Target Customer'
ELSE 'NA'
END)I have executed the above script and command successfully in the below image.
Let’s verify the details by running the below SELECT statement.
USE [AdventureWorksDW2019]
GO
SELECT [FirstName],[MaritalStatus],[Gender],[YearlyIncome],[Status]
FROM [dbo].[NewCustomer]You can see that the column status has been updated as per our criteria in the below image.
Conclusion
I have explained various use cases of the SQL Server CASE WHEN expression with the help of a discount campaign event. For prompting tables and columns when typing SQL queries, it is better to use SSMS add-in, which provides the developer with such a solution as, for example, dbForge SQL Complete which, even in its Express edition, prompts better and more accurately than the built-in SSMS IntelliSense. You can learn and implement these use cases. Please share this article and give your feedback in the comment section so that we can improve.
Tags: sql server, t-sql, t-sql statements Last modified: May 15, 2023
