Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

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
like image 712
ssg31415926 Avatar asked Apr 12 '10 06:04

ssg31415926


3 Answers

SELECT topic, COUNT(name) AS counter
FROM blah
GROUP BY topic
HAVING COUNT(name) <> 1
like image 61
shahkalpesh Avatar answered Nov 18 '22 07:11

shahkalpesh


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
like image 21
Nitin Midha Avatar answered Nov 18 '22 09:11

Nitin Midha


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

like image 25
anonymous user Avatar answered Nov 18 '22 07:11

anonymous user