Need Help With Grouping a Limited Number of Fields in Select Statement 1

[danno64]

I am having trouble figuring out how to
group by only a subset of the fields in a SELECT
statement.

Given this table:

PersonID Dept EmplType Location Bonus
1 IT Manager A 10,000
5 HR Admin B 20,000
9 HR Manager B 20,000
11 IT Analyst A 10,000
14 IT Analyst C 10,000


I want to be able to select all 5 columns in my query.
I want to limit the output to include only the Manager rows...however,
I would like to sum the Bonus by Department and report
this with the rest of the manager row.

The expected output would be

1 IT Manager A 30,000
9 HR Manager B 40,000


I've kind of simplified what I really need to do,
but if I can get some direction on this, I think
I can apply it to my real world query.

Thanks,
Danno

 

[nigelrivett]

select t1.PersonID, t1.Dept, t1.Location, sum(t2.Bonus)
from tbl1 t1
join tbl t2
on t1.Location = t2.Location
where t1.EmplType = 'Manager'
group by t1.PersonID, t1.Dept, t1.Location

or

select
max(case when t1EmplType = 'Manager' then t1.PersonID else '' end),
max(case when t1EmplType = 'Manager' then t1.Dept else '' end),
t1.Location ,
sum(t1.Bonus)
from tbl1 t1
group by t1.Location




======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.