CODEWITHSUNDEEP
sqlgroup-byaggregate-functionsself-join
Safwaa Samin
Safwaa Samin

Reputation: 11

How to calculate total salary for managers, including their own salary, using a single SQL query?

I have an Employees table with the following structure:

ID Name ManagerID Salary
1 Alice NULL 90000
2 Bob 1 80000
3 Charlie 1 85000
4 David 2 75000
5 Eve 2 70000
6 Frank 3 72000

I need a single SQL query to calculate the following:

  1. The total salary for each manager, including their direct reports’ salaries and their own salary.
  2. If a manager has no direct reports, the total should include only their own salary.
  3. The result should include the manager’s name, total salary from direct reports, and their own salary.

What I tried:

Here is the SQL query I wrote:

SELECT e1.Name AS ManagerName, 
       COALESCE(SUM(e2.Salary), 0) + e1.Salary AS TotalSalary
FROM  Employees e1
LEFT JOIN Employees e2 
  ON e1.ID = e2.ManagerID
GROUP BY e1.ID, e1.Name, e1.Salary
ORDER BY TotalSalary DESC;

Problem:

  1. The query works in some cases, but for managers without direct reports, the result is not accurate.
  2. Their salary either appears as NULL or is missing from the total.

Desired Output: For the given table, the output should look like this:

Manager Name Total Salary
Alice 255000
Bob 225000
Charlie 157000
Davi 75000
Eve 70000
Frank 72000

Upvotes: 1

Views: 46

Answers (1)

Siyu Liu
Siyu Liu

Reputation: 1

maybe you can try ISNULL.

I try this

SELECT Name, (IFNULL(SUM(SubSalary), 0) + Salary) AS TotalSalary 
    FROM (SELECT e1.ID, e1.Name, e1.Salary, e2.Salary AS SubSalary FROM Employees e1
    LEFT JOIN Employees e2
        ON e1.ID = e2.ManagerID) AS d
    GROUP BY ID;

and get

Name TotalSalary
Alice 255000
Bob 225000
Charlie 157000
David 75000
Eve 70000
Frank 72000

Upvotes: 0

Related Questions