I'm trying to write a propper SQL query in MS SQL Server. First of all, i have the following tables: Towns, Employees, Addresses. Almost every employee has Manager, whom ManagerID is foreign key in Employees also. (Self relation). My goal is to display the number of managers from each town. So far i have this code:
SELECT t.Name, COUNT(*) AS [Managers from each town] FROM Towns t
JOIN Addresses a
ON t.TownID = a.TownID
JOIN Employees e
ON a.AddressID = e.AddressID
GROUP BY t.Name
ORDER BY [Managers from each town] DESC
This query returns the number of Employees, from each town, not Managers. If i try the second query bellow, I get something totally wrong:
SELECT t.Name, COUNT(*) AS [Managers from each town] FROM Towns t
JOIN Addresses a
ON t.TownID = a.TownID
JOIN Employees e
ON a.AddressID = e.AddressID
JOIN Employees m
ON e.ManagerID = m.ManagerID
GROUP BY t.Name
ORDER BY [Managers from each town] DESC
Here is the structure of 'Employees' table:
EmployeeID, FirstName, LastName, MiddleName, JobTitle,DepartamentID, ManagerID, HireDate, Salary, AddressID
The correct query must return this result set:
Town | Managers from each town
Issaquah | 3
Kenmore | 5
Monroe | 2
Newport Hills | 1
Aucun commentaire:
Enregistrer un commentaire