Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 12, 2007

CASE Function in SQL Server 2005 - part III

By Muthusamy Anantha Kumar aka The MAK

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.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date