SQL: conditionally select column based on aggregate value

Question

The answer to this question is:

Shows the total number of operators hired each year.

:

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

But how do I change this for this question:

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

I tried to do

WHERE Count >= 1

or

WHERE COUNT(YEAR(HireDate)) > 1

but nothing worked. I have been doing research on CASE statements but I couldn't find anything suitable for what I need, any idea?

Using SQL Server 2008

+3

Alde May 15 '17 at 4:29

1 answer

Ashraful Islam · Accepted Answer · 2017-05-15T04:32:50+0000

Use the sentence Having

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