Search code examples
sql-server-2014-express

Select values where Count(*) are greater than a number

I am new at using SQL Server and databases - this is probably very simple.

I am trying to return a value for JobTitle and Count(*) for only those values of Count(*) that exceed 9. I also tried tested some subqueries but could not get it to work. 

Use AdventureWorks2014;

Select 
    JobTitle, Count(*) as Number
From 
    HumanResources.Employee
Where 
    Number > 9
Group By 
    JobTitle;
Solution

  • Move the logic from your WHERE clause into the HAVING clause:

    Select JobTitle, COUNT(*) AS Number
FROM HumanResources.Employee
GROUP BY JobTitle
HAVING COUNT(*) > 9
ORDER BY COUNT(*);   -- or ORDER BY Number