Total Payroll Under Each Manager (Subtree Salary Sum)

WITH RECURSIVE subtree AS (SELECT employee_id AS root_id, employee_id AS desc_id FROM employees UNION ALL SELECT s.root_id, e.employee_id FROM employees e INNER JOIN subtree s ON e.manager_id = s.desc_id) SELECT m.employee_id AS manager_id, m.employee_name AS manager_name, COUNT(s.desc_id) AS subtree_size, SUM(emp.salary) AS total_payroll FROM employees m INNER JOIN subtree s ON s.root_id = m.employee_id INNER JOIN employees emp ON emp.employee_id = s.desc_id WHERE EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = m.employee_id) GROUP BY m.employee_id, m.employee_name ORDER BY total_payroll DESC, m.employee_id;

Why this works

Subtree salary sums are exactly how 'cost center totals' are computed in production. The (root, descendant) shape lets one query compute totals at every level of the org chart simultaneously.

Success check

Each manager appears once with all direct and indirect reports counted exactly once.