CODEWITHSUNDEEP
sql-server
Siva Dss
Siva Dss

Reputation: 9

Want to get the sum of salaries of all the employees under manager

MS SQL Server i want the sum of salaries of all the employees along with manager salary under a single manager

CREATE TABLE #empsal
(
Empid int,
empname varchar(50),
managerid int,
salary int
)

INSERT INTO #empsal
VALUES
(1  ,   'manager'   ,   NULL    ,   500),
(2  ,   'manager1'  ,   NULL    ,   600),
(3  ,   'd' ,   1   ,   100),
(4  ,   'f'     ,   3   ,   200),
(5  ,   'g' ,   4   ,   300),
(6  ,   'h' ,   2   ,   800),
(8  ,   'j' ,   6   ,   200),
(7  ,   'I' ,   6   ,   140),
(8  ,   'j' ,   6   ,   200)

i want the result as below empid sumof salary

1           1100(500+100+200+300)   
2           1940 (600+800+200+140+200)

here employee 3 is having manager as 1 and employee 4 is having manager as 3 and employee 5 is having manager as 4 so over all the employees are under manager 1 . so i want the sum of all salaries under a manager. Please help me to sort out

Upvotes: 0

Views: 768

Answers (1)

EzLo
EzLo

Reputation: 14199

Use a recursive CTE to link the managers to each employee, then group by the highest one.

;WITH Relationships AS
(
    SELECT
        TopManagerID = E.Empid,
        RelatedEmployeeID = E.Empid
    FROM
        #empsal AS E
    WHERE
        E.managerid IS NULL

    UNION ALL

    SELECT
        TopManagerID = E.TopManagerID,
        RelatedEmployeeID = X.Empid
    FROM
        Relationships AS E
        INNER JOIN #empsal AS X ON E.RelatedEmployeeID = X.managerid
)
SELECT 
    R.TopManagerID,
    SumSalary = SUM(E.salary)
FROM
    Relationships AS R
    INNER JOIN #empsal AS E ON R.RelatedEmployeeID = E.Empid
GROUP BY
    R.TopManagerID

Please note that you have 2 employees with ID 8 (should be unique) on your sample data (it will give incorrect results until you update it).

Upvotes: 1

Related Questions