Date: Thu, 18 Mar 2010 11:06:44 -0400
From: Mark Fenbers <Mark.Fenbers@noaa.gov>
Subject: Simple aggregate query brain fart
In-reply-to: <27653.1268835046@sss.pgh.pa.us>
To: pgsql-sql@postgresql.org
Message-id: <4BA24184.7060603@noaa.gov>
Organization: DOC/NOAA/NWS/OHRFC
MIME-version: 1.0
Content-type: multipart/mixed; boundary="Boundary_(ID_9+vjYIQ0f6q+dDiuwMDoew)"
References: <62e0109f1003162359l65b6961ercd14b687433a9ff3@mail.gmail.com>
	<20100317071335.GC25571@a-kretschmer.de>
	<62e0109f1003170130q7a86d8afidf6159dcbd35ade@mail.gmail.com>
	<62e0109f1003170525m6e187b32n58850bd48259daa8@mail.gmail.com>
	<27653.1268835046@sss.pgh.pa.us>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)

This is a multi-part message in MIME format.

--Boundary_(ID_9+vjYIQ0f6q+dDiuwMDoew)
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT

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.  So I modified it to:

SELECT id, count(*) AS cnt FROM mytable WHERE cnt > 2 GROUP BY id;

But Pg still complains (that column cnt does not exist).  When using an 
GROUP/ORDER BY clause, I can refer to a column number (e.g., GROUP BY 1) 
instead of a column name, but how can I refer to my unnamed second 
column in my where clause?

Mark


--Boundary_(ID_9+vjYIQ0f6q+dDiuwMDoew)
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_9+vjYIQ0f6q+dDiuwMDoew)--