Get a list of employees with 3 or more 3 employees from the same city?

Question

I use the following query to find the name of a city with more than 3 employees

SELECT M.NAME FROM MasterCity M INNER JOIN Employee E ON E.CityID = M.ID GROUP BY E.CityID HAVING count(E.CityID) >= 3; 

This gives me the following error

 Column 'MasterCity.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. 

What's wrong. Thanks at Advance

Answer 1

Option number 1 -

 SELECT MAX(M.Name) AS Name FROM MasterCity M JOIN Employee E ON E.CityID = M.ID GROUP BY E.CityID HAVING COUNT(E.CityID) >= 3; 

Option number 2 -

 SELECT M.Name FROM MasterCity M JOIN Employee E ON E.CityID = M.ID GROUP BY E.CityID, M.Name HAVING COUNT(E.CityID) >= 3; 

Answer 2

Try the following:

 SELECT M.ID, M.Name FROM MasterCity M INNER JOIN Employee E ON E.CityID = M.ID GROUP BY M.ID, M.Name HAVING COUNT(E.CityID) >= 3;