WHERE gets processed before GROUP BY in the SELECT statement. How can I use WHERE on the result of 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With