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.<br
/>Mark<br /><br /> Tom Lane wrote: <blockquote cite="mid:11074.1268925073@sss.pgh.pa.us" type="cite"><pre wrap="">Mark
Fenbers<a class="moz-txt-link-rfc2396E" href="mailto:Mark.Fenbers@noaa.gov"><Mark.Fenbers@noaa.gov></a> writes:
</pre><blockquotetype="cite"><pre wrap="">I want to do:
 
SELECT id, count(*) FROM mytable WHERE count(*) > 2 GROUP BY id;   </pre></blockquote><pre wrap="">
</pre><blockquotetype="cite"><pre wrap="">But this doesn't work because Pg won't allow aggregate functions in a 
 
where clause.   </pre></blockquote><pre wrap="">
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
 </pre></blockquote>