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.
CodePudding user response:
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;
CodePudding user response:
SELECT City, COUNT(EmployeeID) AS numofsalesrep
FROM employees
WHERE Title = 'sales representative' AND numofsalesrep >= 2
GROUP BY City
ORDER BY numofsalesrep DESC, City;