if object_id('tempdb..#PayData') is not null
	drop table #PayData
	
create table #PayData
(
	Dept int,
	DeptDesc varchar(5),
	PayDate datetime,
	Pay numeric(7,2)
)

insert #PayData
select 006051, 'CNA', '2013-01-04', 18.53 union all
select 006051, 'CNA', '2013-01-04', 19.16 union all
select 003000, 'RN', '2013-01-04', 3.86 union all
select 003100, 'LPN', '2013-01-04', 11.54 union all
select 006051, 'CNA', '2013-01-04', 3.76 union all
select 005410, 'RN', '2013-01-04', 16.44 union all
select 003100, 'RN', '2013-01-04', 6.08 union all
select 005410, 'RN', '2013-01-04', 12.81 union all
select 805410, 'RN', '2013-01-04', 12.35 union all
select 006051, 'CNA', '2013-01-04', 15.02 union all
select 003000, 'LPN', '2013-01-04', 2.25 union all
select 003100, 'LPN', '2013-01-04', 10.03 union all
select 006051, 'CNA', '2013-01-04', 10.33 union all
select 006051, 'CNA', '2013-01-04', 3.51 union all
select 003000, 'RN', '2013-01-04', 11.91 union all
select 006050, 'LPN', '2013-01-04', 3.53 union all
select 806050, 'RN', '2013-01-04', 12.81 union all
select 006031, 'R.N.', '2013-01-04', 49.86 union all
select 006020, 'LPN', '2013-01-04', 5.67

select * from 
(
	select case when DeptDesc in ('LPN', 'RN', 'R.N.') then 'Nurse' else DeptDesc end as DeptDesc, 
		sum(Pay) over(partition by case when DeptDesc in ('LPN', 'RN', 'R.N.') then 'Nurse' else DeptDesc end ) as TotalPay
	from #PayData
	group by case when DeptDesc in ('LPN', 'RN', 'R.N.') then 'Nurse' else DeptDesc end, Pay
)x
group by DeptDesc, TotalPay