Selenium WebDriver Java Framework Course Limited Time Offer for $20

Selenium WebDriver Java Framework Course Limited Time Offer for $20

 

Print

Case When in SQL Statement

When grouping data in SQL Server, we can evaluate a list of conditions and returns one of multiple possible result expressions. This evaluation can be done by using Case statement in SQL Server.

 

For example, in the product table, we can mark products as 'very cheap', 'cheap', 'moderate','expensive' and 'very expensive' depending on the ListPrice range. See the example below.

 

Use AdventureWorks2008
Go
--Mark PriceLevel according to the ListPrice range and store it as a temp table #a
select ProductID,Name,ListPrice, 
Case When ListPrice between 0 and 10 then 'Very Cheap'
     When ListPrice between 10 and 30 then 'Cheap' 
     when ListPrice between 31 and 60 then 'Moderate' 
     When ListPrice between 61 and 100 then 'Expensive'
     else 'Very Expensive'
     end as PriceLevel
     into #a from Production.Product

--count products by PriceLevel
select PriceLevel,COUNT(*) as 'number of products' from #a
group by PriceLevel

--Delete temp table
drop table #a

Query Result

(504 row(s) affected)
PriceLevel     number of products
-------------- ------------------
Cheap          17
Expensive      23
Moderate       36
Very Cheap     213
Very Expensive 215

(5 row(s) affected)