Oracle hierarchical query: aggregate over each node’s descendants, give results in tree pre-order

hierarchyoracleorder-by

Given hierarchical data, for each item I need to get the sum of a column over the sub-tree rooted at the item, and I need the results in pre-order.

Example: for the data

employee_id | manager_id | salary
------------|------------|-------
          1 |       null |   1000
          2 |          1 |    100
          3 |          1 |    100
          4 |          3 |     10

I need the results

employee_id | total_salary
------------|-------------
          1 |         1210
          2 |          100
          3 |          110
          4 |           10

Oracle docs give the following example:

SELECT name, SUM(salary) "Total_Salary" FROM (
 SELECT CONNECT_BY_ROOT last_name as name, Salary
  FROM employees
  WHERE department_id = 110
  CONNECT BY PRIOR employee_id = manager_id)
  GROUP BY name;

But I also need the rows in tree pre-order, i.e each row should have its descendants immediately after it. Is there an elegant way to do this, since a simple CONNECT BY … START WITH … generally does give its results in pre-order?

Best Answer

This seems to be a working solution (I still wonder if there's a more elegant one):

select name, sum(salary) from (
    select rownum rnum, name, employee_id join_id
    from employees
    connect by manager_id=prior employee_id
    start with manager_id is null
    order siblings by name
  )
  join (
    select salary, connect_by_root employee_id ancestor_id
    from employees
    connect by manager_id=prior employee_id
  ) on join_id=ancestor_id
  group by rnum, name
  order by rnum;
Related Question