Using 'group by' in a recursive SQL query using 'with'

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.

Anyone have any idea about the above question?

I do not guess that I follow what you are after. I do not see a need for a WITH statement here - rather just a self join since all the information you need seems to be in the EMPLOYEES table. Since there is no definition of thr REPORTS table, it is hard to say what that table's purpose is here...

The Reports table is a temporary table computed by the recursive "with" query, hence no other definition of Reports was given in my explanation. The recursive query applies the self-join to the Employees table as you recommended. The reason for the "with" query is that we do not know how many people work under Employee with ID =2, and therefore do not know how many joins on the Employees table we have to apply.

What i am not sure about is if a "group by" can be applied to the recursive "with" query. I am hoping that applying a "group by" would organize the temporary "with" query according to the "Dept" of each employee that satisfies the conditions of the query. The outer query (e.g. "Select * from Reports Group by Site") attempts to query over the temporary Reports relation.

I am not sure if that made things any clearer. Thanks for responding.

Sorry, been on the road...

From what I understand, I would go about it something like this:

Code:

---

    WITH reports_vw
        as (select /*+ materialize */
                  id,
                  department
              from employees
            where boss = 2)
    select id,
          department
      from employees
    where id in (select id from reports_vw)
    /

---

A grouping function is to aggregate something, i.e are you wanting to count the number of employees at each site, the number of departments at each site, the employee with the highest salary per per department per site, and so on...

For example, to "recursively" group by the number of employees in each department at each site, I would just use an analytic:

Code:

---

    WITH reports_vw
        as (select /*+ materialize */
                  id,
                  department
              from employees
            where boss = 2)
    select id,
          department,
          count(id) over (partition by department
                                    order by id) as "# Employees"
      from employees
    where id in (select id
                    from reports_vw)
    /

---

.. at least from what I understand you are trying to accomplish.