0

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?

0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.