Hi and welcome to the forums. The best chance you have of people helping is if you first post ddl and sample data in a consumable format. Most people won't even bother with your post if you post an excel doc. I took the risk this time. I have formatted your data into something consumable to demonstrate what I mean.

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

Now I think that this query will produce the results you are looking for but from the brief description it is hard to say for sure.

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