'I want to Find the number of sales representatives in each city containing at least 2 sales representatives. order by number of employees

I am using the Northwind Database Employees Table. in picture below: I want to Find the number of sales representatives in each city containing at least 2 sales representatives. order by number of employees Using SQL Employees Table Northwind Database

This is my code:My Code AnswerBut it is not showing where there are at least two sales representatives instead it display all cities with sales representatives including the cities with only 1.

mysqlsqlsql-server


Solution 1:[1]

Everything checks about right in your query and you are just missing a HAVING COUNT(*)>=2 clause Try This

SELECT City,COUNT(EmployeeID) AS numofsalesrep
FROM  empolyees
WHERE Title='sales representatives'
GROUP BY City
HAVING COUNT(*)>=2
ORDER BY COUNT(EmployeeID)  DESC, City;

Solution 2:[2]

SELECT City, COUNT(EmployeeID) AS numofsalesrep  
FROM employees 
WHERE Title = 'sales representative' AND numofsalesrep >= 2 
GROUP BY City
ORDER BY numofsalesrep DESC, City;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Henry Ecker
Solution 2 Guldborg92

Related Questions