How do I do a WHERE on the COUNT(name) produced by a GROUP BY clause?

WHERE gets processed before GROUP BY in the SELECT statement. How can I use WHERE on the result o开发者_C百科f a COUNT(name)?

What I want is this:

SELECT topic, COUNT(name) AS counter
FROM blah
GROUP BY name
WHERE counter <> 1

SELECT topic, COUNT(name) AS counter
FROM blah
GROUP BY topic
HAVING COUNT(name) <> 1

I think you are looking for Having Clause:

http://msdn.microsoft.com/en-us/library/ms180199.aspx

SELECT topic, COUNT(name) AS counter
FROM blah
GROUP BY topic
HAVING COUNT(name) <> 1

as the other have answered you need a HAVING.

WHERE filters the rows remaining after all joins

GROUP BY combines rows into groups

HAVING filters those groups

don't worry abut repeating the COUNT(*) in the SELECT list and the having, the optimizer is smart enough to optimize this of most databases