SQL : Conditionally select column based on aggregate value

Question

The answer to this question:

Display the total number of operators hired each year.

is this:

SELECT
    COUNT(YEAR(HireDate)) AS 'Count',
    YEAR(HireDate) AS 'Year'
FROM Operators
GROUP BY YEAR(HireDate);

But how would I modify that for this question:

Display the number of operators hired in each year where more than one operator was hired.

I tried to do

WHERE Count >= 1

or

WHERE COUNT(YEAR(HireDate)) > 1

but nothing worked. I did research CASE statements but I couldn't find anything suiting what I need to do, any idea?

Using SQL Server 2008

Answer 1

Use Having clause

SELECT
    COUNT(YEAR(HireDate)) AS 'Count',
    YEAR(HireDate) AS 'Year'
FROM Operators
GROUP BY YEAR(HireDate)
HAVING Count >= 1;

Read More