|
|
The mission is to calculate total salary of employees
that work under supervision of specific manager. Manager id is
stored in the field "ManagerNo". Every employee have this fields filled with
id of some other employee, who is his/her manager. Only CEO john silver have
NULL, because he stands on the top and nobody above him.
| EmpNo | EmpName | ManagerNo | Salary | ... |
| 1 | john silver | NULL | 210000 | ... |
| 2 | gret garbo | 1 | 65000 | ... |
| 3 | le chen | 2 | 80000 | ... |
| ... | ... | ... | ... | ... |
The query uses "Self Join" on two instances of Employees table :
SELECT a.EmpNo, TRIM(a.FirstName) & ' ' & TRIM(a.LastName) AS EmpName,
SUM(b.Salary) AS TotalMoney
FROM Employees AS a INNER JOIN Employees AS b ON a.EmpNo=b.ManagerNo
GROUP BY a.EmpNo, TRIM(a.FirstName) & ' ' & TRIM(a.LastName)
ORDER BY SUM(b.Salary) DESC;
|
With following result:
| EmpNo | EmpName | TotalMoney |
| 1 | john silver | 298000 |
| 8 | dona fiber | 215500 |
| 2 | gret garbo | 185000 |
| 5 | maggi forth | 141000 |
| 7 | anna poperplatz | 95000 |
|