Query to select the manager and total salary of employees under the manager

I need to write a query to select the manager and total salary of employees under the manager.

The salary of employees should roll up to Manager based on hierarchy. Mgrid references to empid in the same table.

Table : Employee

EmpId  Name      Salary  Mgrid
:---- |:------:| :------:|-----:| 
1      CEO        10000    NULL
2      M2         8000      1
3      M3         6000      2
4      M4         6000      2
5      M5         5000      4
6      M6         1000      2
7      M7         500       5

Output should be ManagerName and Salary

Name     TotalSalary
----------------------
CEO      26,500  (total salary of all employees under him/her)
M2       18,500  (Salary of M3 +M4 + M5 + M6 + M7). 
M4        5,500  (salary of M5+M7)
M5          500  (Salary Of M7)

In the above example : M2 has M3, M4, M6 directly under him. Also M4 has M5 under him and M5 has M7 under him. So those employees also should be included in the total for M2.

I tried using recursive CTE but lost in rolling up the salary.

Any help is appreciated.

Query :

Create table Emp_Manager
(
 Empid int identity,
 EmpName varchar(100),
 Salary int,
 Mgrid int)

 insert into Emp_Manager  values('CEO',10000,NULL),('M2',8000,1),('M3',6000,2)
 ,('M4',6000,2),('M5',5000,4),('M6',1000,2),('M7',500,5)


 ;With Cte_manager as
     (Select Empid,Empname , Salary,Mgrid,0 AS Hierchacy  FROM Emp_Manager WHERE mgrid is null
      union all
      Select e.Empid,e.Empname ,e.Salary,e.Mgrid, Hierchacy + 1 AS Hierchacy FROM Emp_Manager as E
      inner join Cte_manager as c on e.mgrid = c.empid   
        ) ,
      cte_emp as (Select Empid,empname , Hierchacy,
      Sum(Salary) Over (order by Hierchacy rows between 1 following and unbounded following) AS Salary
      from Cte_manager)
      Select Empname  AS Managername,Salary as TotalSal FROM cte_emp as c
      WHERE Exists (Select 1 FROM Emp_Manager WHERE mgrid = c.empid)
      order by Hierchacy


Query  result: 
CEO 26500
M2  18500
M4  11500
M5  500

M4 is Wrong… The way I rolling up using hierarchy is not right. I couldn’t think of any other way,

Answer

The solution below assigns managers a salary of 0 as a start for the recursive sum of their employee salaries. This eliminates the need for a Hierarchy column, a second CTE cte_emp or a complex sum aggregation (Sum(Salary) Over (order by Hierchacy rows between 1 following and unbounded following))

Sample data

create table Employee
(
  EmpId int,
  Name nvarchar(3),
  Salary money,
  MgrId int
);

insert into Employee (EmpId, Name, Salary, MgrId) values
(1, 'CEO', 10000, NULL),
(2, 'M2',   8000, 1),
(3, 'M3',   6000, 2),
(4, 'M4',   6000, 2),
(5, 'M5',   5000, 4),
(6, 'M6',   1000, 2),
(7, 'M7',    500, 5);

Solution

with rcte as
(
  select e.Name as ManagerName,
         convert(money, 0) as Salary,
         e.EmpId as EmpId
  from Employee e
  where exists ( select 'x'
                 from Employee ee
                 where ee.MgrId = e.EmpId)
union all
  select r.ManagerName,
         e.Salary,
         e.EmpId
  from rcte r
  join Employee e
    on e.MgrId = r.EmpId
)
select r.ManagerName,
       sum(r.Salary) as TotalSalary
from rcte r
group by r.ManagerName;

Result

ManagerName  TotalSalary
-----------  -----------
CEO          26500.0000
M2           18500.0000
M4            5500.0000
M5             500.0000

Fiddle to see things in action with intermediate steps.