A group by query question

[eddy04]

I am trying to make a SQL query in MS SQL Server that groups people togother by department and then has 2 columns. Each column is their status whether employee or manager. The field contains a count of how man people fit the criteria. The following code works for one column but i how do you
put in a second column with another WHERE statement?

SELECT dept, COUNT(*) AS manager,
FROM allEmployees
where (status = 'manager')
GROUP BY dept

| managers | employees
Dept1 | 3 | 5
Dept2 | 6 | 2

Any help would be greatly appreciated. Thanks.

[skhanal]

Are you trying to make it work for just 2 columns or you want it to be generic.

For two column, you may use a derived table like

select a.dept, a.oneyear, b.twoyear
from
(select dept, count(*) as oneyear
from yourtable
group by dept
having datediff(year, startdate, getdate()) between 1 and 2) as a
inner join
(select dept, count(*) as twoyear
from yourtable
group by dept
having datediff(year, startdate, getdate()) >= 2) as b
on a.dept = b.dept

I have not tested it, but it might work.