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
Fig 1.0
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
Fig 1.1
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.
