Total salary from a hierarchical sql query in Oracle

Question

I am trying to find the total salary by using oracle hierarchical SQL query but I do not get the desired output.

I use Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit.

Below are the sample input tables and the hierarchical structure.

Below is the desired output table.

Below is the code I wrote but its not summing up at all levels.

SELECT COALESCE(e.Manager_id, e.Employee_id) Employee_id,
       (SELECT Employee_name
          FROM Employee_table
         WHERE Employee_id = COALESCE(Manager_id, Employee_id)) Employee_name,
       SUM(s.Employee_salary)
  FROM Employee_table e
  JOIN Salary_table s
    ON s.Employee_id = e.Employee_id
 WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR s.Manager_id = s.Employee_id
 GROUP BY COALESCE(e.Manager_id, e.Employee_id)
 ORDER BY SUM(s.Employee_salary) DESC;

What am I doing wrong here?

@Gordon Linoff: I have updated my original question with the version am using. — anishjp, Apr 18 '20 at 15:41
The disired output for employee:id=1 contains his salary, while the output for employee:id=2 employee:id=3 exclude their salaries, also employee:id=10 doesn't exist in salary table, so what the rule here to be applied to get the disired output? — Eng. Samer T, Apr 18 '20 at 15:49
Eng. Samer T: I have corrected the output table. Employee 3 and Employee 10 do not have a salary. — anishjp, Apr 18 '20 at 16:00

score 0 · Answer 1 · answered Apr 18 '20 at 16:31

Here is on approach using a standard recursive query rather than Oracle specific connect by syntax:

with cte (employee_id, employee_name, child_id) as (
    select employee_id, employee_name, employee_id from employee where manager_id is null
    union all
    select c.employee_id, c.employee_name, e.employee_id
    from employee e
    inner join cte c on e.manager_id = c.child_id
)
select c.employee_id, c.employee_name, sum(s.employee_salary) total_salary
from cte c
inner join salary s on s.employee_id = c.child_id
group by c.employee_id, c.employee_name
order by c.employee_id

The recursive query starts from employees that have no manager, and retrieves the children record of each node. Then, the outer query brings the salary table, and aggregate by "root employee".

Demo on DB Fiddle:

EMPLOYEE_ID | EMPLOYEE_NAME | TOTAL_SALARY
----------: | :------------ | -----------:
          1 | John          |         4000
          2 | Philip        |        17900
          9 | Joe           |         5700

Slkrasnodar · Answer 2 · 2020-04-18T17:57:33.037

    with Salary_table (Employee_id, Employee_salary) as (
    select 1, 4000 from dual union all
    select 2, 2500 from dual union all
    select 4, 3400 from dual union all
    select 5, 4500 from dual union all
    select 6, 4300 from dual union all
    select 7, 2000 from dual union all
    select 8, 1200 from dual union all
    select 9, 3100 from dual union all
    select 11, 2600 from dual 
    )
    , Employee_table (Employee_id, Employee_name, Manager_id) as (

    select 1, 'John', null from dual union all
    select 2, 'Phil', null from dual union all
    select 3, 'Rayan', 2 from dual union all
    select 4, 'Peter', 2 from dual union all
    select 5, 'Mark', 2 from dual union all
    select 6, 'Steve', 3 from dual union all
    select 7, 'Margret', 3 from dual union all
    select 8, 'Paul', 3 from dual union all
    select 9, 'Joe', null from dual union all
    select 10, 'Bose', 9 from dual union all
    select 11, 'Jane', 9 from dual 
    )
    select mgr_id, mgr_name, sum(employee_salary) from (
      select employee_id, connect_by_root employee_id mgr_id,
             connect_by_root employee_name mgr_name 
      from 
      employee_table e
      start with manager_id is null
      connect by prior employee_id = manager_id 

    ) 
    join salary_table 
    using(employee_id)
    group by mgr_id, mgr_name
    order by 1;

    MGR_ID  MGR_NAM  SUM(EMPLOYEE_SALARY)
----------  -------  --------------------
         1  John                     4000  
         2  Phil                    17900  
         9  Joe                      5700

removed the redundant check in the CONNECT BY clause. – Slkrasnodar Apr 18 '20 at 17:58 — Slkrasnodar, Apr 18 '20 at 17:58

Total salary from a hierarchical sql query in Oracle

2 Answers2