Re: Simple aggregate query brain fart

Thanks, Joe and Tom. You cleared the webs out of my brain. I used HAVING before, but not lately and I got rusty.

Mark

Tom Lane wrote:

Mark Fenbers Mark(dot)Fenbers(at)noaa(dot)gov writes:


I want to do:
SELECT id, count(*) FROM mytable WHERE count(*) 2 GROUP BY id;




But this doesn't work because Pg won't allow aggregate functions in a
where clause.


Use HAVING, not WHERE. The way you are trying to write the query is
meaningless because WHERE filters rows before grouping/aggregation.
HAVING filters afterwards, which is when it makes sense to put a
condition on count(*).

regards, tom lane