CODEWITHSUNDEEP
sqlsql-servert-sql
FarCry88
FarCry88

Reputation: 15

Troubles with using GROUP BY in SQL Query

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

Upvotes: 0

Views: 123

Answers (4)

Manoj Xtreme
Manoj Xtreme

Reputation: 1

You can try the following query to get your required output...

select t.TownName, COUNT(*) as No from Town t
Inner Join Address a on a.TownID = t.TownID
inner join Employee e on e.ManagerID = a.EmployeeID
Group By t.TownName

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13248

Try:

select t.name,
       count(*) as num_managers
  from employees m
  join addresses a
    on m.addressid = a.addressid
  join towns t
    on a.townid = t.townid
 where exists (select 1 from employees x where x.managerid = m.employeeid)
 group by t.name
 order by 2 desc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I think the following variation on your original query should could the managers who reside in each town:

SELECT t.Name, COUNT(DISTINCT e.EmployeeId) AS [Managers from each town]
FROM Towns t JOIN
      Addresses a
      ON t.TownID = a.TownID JOIN
      Employees e
      ON a.AddressID = e.AddressID
WHERE e.EmployeeId IN (SELECT e2.ManagerId FROM Employees e2)
GROUP BY t.Name
ORDER BY [Managers from each town] DESC;

The DISTINCT may not be necessary, but it is hard to say without better understanding the data structure.

Upvotes: 0

Me.Name
Me.Name

Reputation: 12544

If I understand your structure correctly, the only indication an Employee is a manager is if its id is used as managerid for other employees. Your first query already correctly displays the count, then all that is needed is to filter the results with something like

where EmployeeID in (select ManagerID from Employees)

therefore turning your first query into :

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
where EmployeeID in (select ManagerID from Employees)
GROUP BY t.Name
ORDER BY [Managers from each town] DESC

Upvotes: 1

Related Questions