Question 419581 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to find salaries of their subordinates

This is my data ,

employee_id name manager_id salary
1 Alice 100000.00
2 Bob 1 80000.00
3 Charlie 1 75000.00
4 David 2 60000.00
5 Eve 2 50000.00
6 Frank 3 55000.00

Question is The total salary paid under each employee (including their own salary and all the salaries of their subordinates, directly or indirectly).

I tried hierarchical way and achieved below result.

employee_id name manager_id salary path total_salary
1 Alice 100000.00 boss 100000
2 Bob 1 80000.00 boss->Bob 180000
3 Charlie 1 75000.00 boss->Charlie 175000
4 David 2 60000.00 boss->Bob->David 240000
5 Eve 2 50000.00 boss->Bob->Eve 230000
6 Frank 3 55000.00 boss->Charlie->Frank 230000

but my expectation is for Alice should be 430000.00,Bob ->190000.00,Charlie ->

employee_id name manager_id salary path total_salary
1 Alice NULL 100000.00 boss 430000.00
2 Bob 1 80000.00 boss->Bob 190000.00
3 Charlie 1 75000.00 boss->Charlie 130000.00
4 David 2 60000.00 boss->Bob->David 60000.00
5 Eve 2 50000.00 boss->Bob->Eve 50000.00
6 Frank 3 55000.00 boss->Charlie->Frank 55000.00
Tags:

2

Answers


  1. 0

    This can be done with a recursive cte to find all the subordinates for each person in the employee table and then their salaries merely need to be summed to calculate the total salary.

    create table employee (
    employee_id integer primary key,
    name varchar not null,
    manager_id integer,
    salary integer not null
    );

insert into employee (employee_id, name, manager_id, salary)
values (1, 'Alice', null, 100000),
(2, 'Bob', 1, 80000),
(3, 'Charlie', 1, 75000),
(4, 'David', 2, 60000),
(5, 'Eve', 2, 50000),
(6, 'Frank', 3, 55000);


with recursive cte (apex_id, employee_id, salary) as (
    select employee_id, employee_id, salary
    from employee
    union all
    select cte.apex_id, employee.employee_id, employee.salary
    from cte
    join employee on employee.manager_id = cte.employee_id
    )
select employee.*, sum(cte.salary) as total_salary
from cte
join employee on employee.employee_id = cte.apex_id
group by employee.employee_id
order by employee.employee_id;
    Login or Signup to reply.
  2. 0
    1. For each employee (root_id) recursively collect all their subordinates, directly or indirectly.
      There we save root id for employee and join other rows of hierarchy tree in recursion.
    from r inner join Salaries t on t.manager_id=r.employee_id

    We get (for Alice) Alice as child of level 1, Charlie and Bob as childs of level 2…

    lvl root_id root_name employee_id name salary path
    1 1 Alice 1 Alice 100000 Alice
    2 1 Alice 3 Charlie 75000 Alice->Charlie
    2 1 Alice 2 Bob 80000 Alice->Bob
    3 1 Alice 5 Eve 50000 Alice->Bob->Eve
    3 1 Alice 4 David 60000 Alice->Bob->David
    3 1 Alice 6 Frank 55000 Alice->Charlie->Frank
    1 2 Bob 2 Bob 80000 Bob
    2 2 Bob 5 Eve 50000 Bob->Eve
    2 2 Bob 4 David 60000 Bob->David
    1 3 Charlie 3 Charlie 75000 Charlie
    2 3 Charlie 6 Frank 55000 Charlie->Frank
    1 4 David 4 David 60000 David
    1 5 Eve 5 Eve 50000 Eve
    1 6 Frank 6 Frank 55000 Frank
    1. Aggregate all salaries for root_id.

    See example

    employee_id name manager_id salary
    1 Alice null 100000
    2 Bob 1 80000
    3 Charlie 1 75000
    4 David 2 60000
    5 Eve 2 50000
    6 Frank 3 55000 
    with  recursive 
  r as(
  select 1 lvl,employee_id, name, manager_id, salary
     ,employee_id as root_id,name as root_name,manager_id root_manager_id,salary root_salary
     ,cast(concat(name,'') as varchar(100)) path
  from Salaries
  union all
  select lvl+1 lvl,t.employee_id, t.name, t.manager_id, t.salary
     ,r.root_id,r.root_name,root_manager_id,root_salary
     ,cast(concat(r.path,'->',t.name)  as varchar(100)) path
  from r inner join Salaries t on t.manager_id=r.employee_id
)
select root_id employee,min(root_name) name,min(root_manager_id)manager_id
  ,min(root_salary) salary
  ,sum(salary) total_salary
  ,string_agg(r.path,',') tot_path
from r
group by root_id
order by root_id
    employee_id name manager_id salary total_salary tot_path
    1 Alice null 100000 420000 Alice,Alice->Bob,Alice->Charlie,Alice->Bob->David,Alice->Bob->Eve,Alice->Charlie->Frank
    2 Bob 1 80000 190000 Bob,Bob->David,Bob->Eve
    3 Charlie 1 75000 130000 Charlie,Charlie->Frank
    4 David 2 60000 60000 David
    5 Eve 2 50000 50000 Eve
    6 Frank 3 55000 55000 Frank

    fiddle

    Minimize query, excluding code part for debugging and clarity

    with  recursive r as(
  select employee_id as root_id,name as root_name,manager_id root_manager_id,salary root_salary
     ,employee_id, salary
  from Salaries
  union all
  select r.root_id,r.root_name,root_manager_id,root_salary
     ,t.employee_id, t.salary
  from r inner join Salaries t on t.manager_id=r.employee_id
)
select root_id,min(root_name) name,min(root_manager_id)manager_id
  ,min(root_salary) salary
  ,sum(salary) total_salary
from r
group by root_id

    If you want get all columns from your table and only add totals column

    with  recursive r as(
  select employee_id as root_id ,employee_id, salary
  from Salaries
  union all
  select r.root_id ,t.employee_id, t.salary
  from r inner join Salaries t on t.manager_id=r.employee_id
)
select s.*,t.total_salary
from salaries s 
inner join (
     select root_id,sum(salary) total_salary
     from r
     group by root_id
  ) t  on t.root_id=s.employee_id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search