Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 6D4EF637A24
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 18 Mar 2010 12:21:32 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 65458-05 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 18 Mar 2010 15:20:56 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mmp3.nems.noaa.gov (mmp3.nems.noaa.gov [140.90.121.158])
	by mail.postgresql.org (Postfix) with ESMTP id 03BC2632FB3
	for <pgsql-sql@postgresql.org>; Thu, 18 Mar 2010 12:15:58 -0300 (ADT)
Received: from [198.206.42.50] by mmp3.nems.noaa.gov
	(Sun Java System Messaging Server 6.2-2.05 (built Apr 28 2005))
	with ESMTPSA id <0KZH007M4H2J2720@mmp3.nems.noaa.gov> for
	pgsql-sql@postgresql.org; Thu, 18 Mar 2010 11:15:56 -0400 (EDT)
Date: Thu, 18 Mar 2010 11:15:55 -0400
From: Mark Fenbers <Mark.Fenbers@noaa.gov>
Subject: Re: Simple aggregate query brain fart
In-reply-to: <11074.1268925073@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Message-id: <4BA243AB.10804@noaa.gov>
Organization: DOC/NOAA/NWS/OHRFC
MIME-version: 1.0
Content-type: multipart/mixed; boundary="Boundary_(ID_9ZQ2a9FJsoeZ1RQJmwthyw)"
References: <62e0109f1003162359l65b6961ercd14b687433a9ff3@mail.gmail.com>
	<20100317071335.GC25571@a-kretschmer.de>
	<62e0109f1003170130q7a86d8afidf6159dcbd35ade@mail.gmail.com>
	<62e0109f1003170525m6e187b32n58850bd48259daa8@mail.gmail.com>
	<27653.1268835046@sss.pgh.pa.us> <4BA24184.7060603@noaa.gov>
	<11074.1268925073@sss.pgh.pa.us>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=AWL=-0.001,
	BAYES_00=-2.599, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201003/148
X-Sequence-Number: 33855

This is a multi-part message in MIME format.

--Boundary_(ID_9ZQ2a9FJsoeZ1RQJmwthyw)
Content-type: text/html; charset=ISO-8859-1
Content-transfer-encoding: 7BIT

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Thanks, Joe and Tom.&nbsp; You cleared the webs out of my brain.&nbsp; 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">&lt;Mark.Fenbers@noaa.gov&gt;</a> writes:
  </pre>
  <blockquote type="cite">
    <pre wrap="">I want to do:
SELECT id, count(*) FROM mytable WHERE count(*) &gt; 2 GROUP BY id;
    </pre>
  </blockquote>
  <pre wrap=""><!---->
  </pre>
  <blockquote type="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>
</body>
</html>

--Boundary_(ID_9ZQ2a9FJsoeZ1RQJmwthyw)
Content-type: text/x-vcard; charset=utf-8; name=Mark_Fenbers.vcf
Content-transfer-encoding: 7BIT
Content-disposition: attachment; filename=Mark_Fenbers.vcf

begin:vcard
fn:Mark Fenbers
n:Fenbers;Mark
org:National Weather Service;Ohio River Forecast Center
adr:;;1901 South OH-134;Wilmington;OH;45177-1908;USA
email;internet:Mark.Fenbers@noaa.gov
title:Sr. HAS Meteorologist
tel;work:937-383-0430
tel;fax:937-383-0033
note:"HAS" is an acronym for Hydrometeorological Analysis and Support
x-mozilla-html:TRUE
url:http://weather.gov/ohrfc
version:2.1
end:vcard


--Boundary_(ID_9ZQ2a9FJsoeZ1RQJmwthyw)--