|
|
Suppose that we want to locate employees of Colombo-Ltd
according to the building they work in. Employees table does not include
a number of building, but it can be retrieved from another table - departments:
USE colombo;
/*
this is a simple join between employees and departments tables
*/
SELECT e.EmpNo, e.DeptNo, e.FirstName, e.LastName, e.Salary, d.BuildingNum
FROM employees AS e
INNER JOIN departments AS d
ON e.DeptNo = d.DeptNo
|
Query results will look like this:
| EmpNo | DeptNo | FirstName | LastName | Salary | BuildingNum |
| 1 | 1 | john | silver | 210000 | 1 |
| 2 | 1 | gret | garbo | 65000 | 1 |
| 3 | 2 | lee | chen | 80000 | 2 |
| 14 | 2 | suzan | shpeer | 81000 | 2 |
| 4 | 3 | daniel | defo | 70500 | 3 |
Now we can calculate total salary amount per office building using GROUP BY:
USE colombo;
/*
to show biggest amount first we need to add DESC at the end of ORDER clause
*/
SELECT t1.BuildingNum,
SUM(t1.Salary) AS total_salary
FROM
(
SELECT e.EmpNo, e.DeptNo, e.FirstName, e.LastName,
e.Salary, d.BuildingNum
FROM employees AS e
INNER JOIN departments AS d
ON e.DeptNo = d.DeptNo
) AS t1
GROUP BY t1.BuildingNum
ORDER BY SUM(t1.Salary) DESC;
|
Query results will look like this:
| BuildingNum | total_salary |
| 4 | 617500 |
| 1 | 275000 |
| 3 | 246000 |
| 2 | 161000 |
|