Source table:

create table test_table ( id integer not null, name char(1) not null, parent_id integer, sum integer not null); INSERT INTO test_table VALUES(1, 'A', null, 300); INSERT INTO test_table VALUES(11, 'B', 1, 2340); INSERT INTO test_table VALUES(111, 'C', 11, 3200); INSERT INTO test_table VALUES(22, 'D', 1, 7540); INSERT INTO test_table VALUES(2, 'E', null, 6300); INSERT INTO test_table VALUES(3, 'F', 2, 8600); INSERT INTO test_table VALUES(101, 'G', 3, 3800); INSERT INTO test_table VALUES(102, 'H', 3, 9700);

Here is the code:

 WITH sum_bum ( sum, id, parent_id ) AS ( SELECT 0, id, parent_id FROM test_table WHERE parent_id is NULL UNION ALL SELECT s.sum + t.sum, t.id, t.parent_id FROM test_table t INNER JOIN sum_bum s ON ( s.id = t.parent_id ) ) SELECT s.sum, ttt.name FROM sum_bum s join test_table ttt on ttt.id = s.id order by ttt.name;

Example output values:

  13080 - А 3200 - B NULL - C NULL - D 22100 - E 13500 - F NULL - G NULL - H

In a good way, he should consider the salary of all subordinates, but considers the salary only of those who have bosses. Those. all the way around

  • one
    Without a structure and an example of a table, you are too lazy to understand your request to understand where you have an error. - Denis

1 answer 1

If you need to calculate the amount only for root records, then write so:

 WITH sum_bum (id, root_id ) AS ( SELECT id, id as root_id FROM test_table WHERE parent_id is NULL UNION ALL SELECT t.id, s.root_id FROM test_table t INNER JOIN sum_bum s ON ( s.id = t.parent_id ) ) SELECT ttt.name, sum(ttt1.sum) FROM sum_bum s join test_table ttt on ttt.id = s.root_id join test_table ttt1 on ttt1.id = s.id where s.id != s_root_id group by ttt.name order by ttt.name;

The point is that you need to determine the root employee for each of the subsidiaries (regardless of the level of the hierarchy) and then just sum up their salaries. Since it is only necessary to calculate the salaries of subordinates, in the where clause we remove the line with the employee himself.

  • It is almost what you need. It is necessary to find the salaries of subordinates of all workers who have subordinates. But I myself will try to implement it - daydark
  • @daydark, just remove the filter from the first part of the recursive cte filter "WHERE parent_id is NULL" - then subordinates will be searched for all employees, not just the root ones. Well, a little bit, you need to change the final request itself, so that null are output for employees without subordinates (in the current request they simply will not be displayed - they will be cut off by the condition "where s.id! = S_root_id" - minamoto
  • I didn’t eat something with the final query - daydark
  • @daydark, try this: SELECT ttt.name, sum (ttt1.sum) FROM sum_bum s join test_table ttt on ttt.id = s.root_id left join test_table ttt1 on ttt1.id = s.id and s.id! = s_root_id group by ttt.name order by ttt.name; - minamoto
  • Yes, it turned out, thanks) - daydark