Is it possible to use a "group by" clause in a recursive SQL query that uses 'with'? For example, suppose we have the following relation. Employees(Id, Name, Boss, Dept, Site). For arguments sake, say that we are interested in knowing for each department at each site all of the people that report to employee with id =2 (this includes all people "below" employee 2, so people that have boss who's boss is employee 2). I know that using the SQL 'with' block that the query for all employees that have employee id=2 as their boss can be expressed as follows:

With Reports(Id)
AS
(select Id from Employees where Boss=2
UNION ALL
select E.id From Employees E, Reports R
where R.id = E.Boss)

Select * from Reports

However, I am not sure about how to handle the "each department at each site" part of the query. Can I include two "group by" in the above query?

With Reports(Id)
AS
(select Id from Employees where Boss=2
UNION ALL
select E.id From Employees E, Reports R
where R.id = E.Boss
Group by E.Dept)

Select * from reports
Group by Site

Will the outer query iterate through each of the "Dept" computed in the first query, and compute the result for each Site at each department? I wish I had a "for loop" to iterate over each "group by" in the inner query. Thanks in advance for the help.