Get the list of Employees in which 3 or more than 3 employees are from same city?

Question

I am using the below query to find the City Name having 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;

It 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 is wrong.? Thanks in Advance

Answer 1

Variant #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;

Variant #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 this:

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;