CASE Function in SQL Server 2005 - part III

April 12, 2007

In Part I and Part II of this series, we have illustrated how to use simple case expressions in queries. In this installment, I am going to illustrate how to use case functions in clauses such as group by.

Method 6: Usage of simple case function in GROUP BY clause

Let us assume we have the following table.

set quoted_identifier off
go
use tempdb
go
if exists (select * from dbo.sysobjects where id = object_id(N'[emp]') 
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [emp]
GO
create table Emp (id int, [First name] varchar(50), 
 [Last name] varchar(50), Salary money, state char(2))
go
insert into Emp (id,[First name],[Last name], salary, State ) 
 values (1,'John','Smith',120000,'WA')
insert into Emp (id,[First name],[Last name], salary, State ) 
 values (2,'James','Bond',95000,'OR')
insert into Emp (id,[First name],[Last name], salary , State) 
 values (3,'Alexa','Mantena',200000,'WY')
insert into Emp (id,[First name],[Last name], salary, State ) 
 values (4,'Shui','Qui',36000,'CO')
insert into Emp (id,[First name],[Last name], salary, State ) 
 values (5,'William','Hsu',39000,'NE')
insert into Emp (id,[First name],[Last name], salary , State) 
 values (6,'Danielle','Stewart',50000,'TX')
insert into Emp (id,[First name],[Last name], 
 salary , State) values (7,'Martha','Mcgrath',400000,'PA')
insert into Emp (id,[First name],[Last name], 
 salary, State ) values (8,'Henry','Fayol',75000,'NJ')
insert into Emp (id,[First name],[Last name], 
 salary, State ) values (9,'Dick','Watson',91000,'NY')
insert into Emp (id,[First name],[Last name], 
 salary, State ) values (10,'Helen','Foster',124000,'AK')
go

Let us assume that we want to create a column, TimeZone, on the fly, based on the State.

select id,[First name],[Last name], salary, Timezone = case 
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in 
 ('NY','NJ','VT','ME','NH','MA','RI','CT','PA','DE','MD',
 'DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') then 'Eastern' 
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in 
 ('ND','SD','NE','KS','OK','TX','MN','IA','MO','AR','LA','WI','IL',
 'TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end
from emp

This would produce the following result shown below.

id First name Last name     salary      Timezone
-----------------------------------------------------------------------
1 John Smith  120000.00 Pacific
2 James Bond  95000.00 Pacific
3 Alea Mantena  200000.00 Mountain
4 Shui Qui  36000.00 Pacific
5 William Hsu  39000.00 Pacific
6 Danielle Stewart  50000.00 Central
7 Martha Mcgrath  400000.00 Eastern
8 Henry Fayol  75000.00 Eastern
9 Dick Watson  91000.00 Eastern
10 Helen Foster  124000.00 Alaskan

Now let us assume we want to see all of the rows that belong to the Eastern and Mountain Time zone.

select * from (
select id,[First name],[Last name], salary, Timezone = case 
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in 
 ('NY','NJ','VT','ME','NH','MA','RI','CT','PA','DE','MD',
 'DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') then 'Eastern' 
when state in 
 ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in 
 ('ND','SD','NE','KS','OK','TX','MN','IA','MO','AR','LA',
 'WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Hawaii' end
from emp) as mytype where TimeZone in ('Mountain','eastern')

This would produce the following result.

id First name Last name       salary       Timezone
3 Alexa Mantena 200000.00  Mountain
7 Martha Mcgrath 400000.00  Eastern
8 Henry Fayol  75000.00  Eastern
9 Dick Watson  91000.00  Eastern

Let us assume that we have the above table and we want to display the average salary, based on the time zone.

Execute the following SQL Statement.

select avg(salary) as AverageSalary, Timezone = case 
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in ('NY','NJ','VT','ME','NH','MA','RI','CT','PA','DE','MD','DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') then 'Eastern' 
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in ('ND','SD','NE','KS','OK','TX','MN','IA','MO','AR','LA','WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end
from emp group by 
case 
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in ('NY','NJ','VT','ME','NH','MA','RI','CT','PA','DE','MD','DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') then 'Eastern' 
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in ('ND','SD','NE','KS','OK','TX','MN','IA','MO','AR','LA','WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end

This would produce the following result, shown below. Refer Fig 1.0

AverageSalary TimeZone
-------------------------------
124000.00 Alaskan
50000.00 Central
188666.6666 Eastern
200000.00 Mountain
72500.00 Pacific

Let us assume that we want to see only the Eastern and Alaskan time zone from this result. We could use the HAVING clause, as shown below.

select avg(salary) as AverageSalary, Timezone = case 
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in ('NY','NJ','VT','ME','NH','MA','RI','CT','PA','DE','MD','DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') then 'Eastern' 
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in ('ND','SD','NE','KS','OK','TX','MN','IA','MO','AR','LA','WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end
from emp group by 
case 
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in ('NY','NJ','VT','ME','NH','MA','RI','CT','PA','DE','MD','DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') then 'Eastern' 
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in ('ND','SD','NE','KS','OK','TX','MN','IA','MO','AR','LA','WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end 
having 
case 
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in ('NY','NJ','VT','ME','NH','MA','RI','CT','PA','DE','MD','DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') then 'Eastern' 
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in ('ND','SD','NE','KS','OK','TX','MN','IA','MO','AR','LA','WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end 
in ('Eastern','Alaskan')

This would produce the following result, shown below. Fig 1.1

AverageSalary TimeZone
-----------------------------
124000.00 Alaskan
188666.6666 Eastern

Conclusion

Part I, Part II and Part III of this series has illustrated the usage of case expressions in SQL Server in various situations. The next series is going to illustrate how to use case statements in “IN” clause.

» See All Articles by Columnist MAK