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
(504 row(s) affected) PriceLevel number of products -------------- ------------------ Cheap 17 Expensive 23 Moderate 36 Very Cheap 213 Very Expensive 215 (5 row(s) affected)