Results 1 to 5 of 5

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

  1. #1
    Join Date
    Mar 2006
    Posts
    3

    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.

  2. #2
    Join Date
    Mar 2006
    Posts
    3
    Anyone have any idea about the above question?

  3. #3
    Join Date
    Mar 2006
    Posts
    2
    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...

  4. #4
    Join Date
    Mar 2006
    Posts
    3
    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.

  5. #5
    Join Date
    Mar 2006
    Posts
    2
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •