From: Mark Fenbers <Mark.Fenbers@noaa.gov>
To: pgsql-sql@postgresql.org
Subject: Simple aggregate query brain fart
Date: Thu, 18 Mar 2010 11:06:44 -0400
Message-ID: <4BA24184.7060603@noaa.gov> (raw)
In-Reply-To: <27653.1268835046@sss.pgh.pa.us>
References: <62e0109f1003162359l65b6961ercd14b687433a9ff3@mail.gmail.com>
<20100317071335.GC25571@a-kretschmer.de>
<62e0109f1003170130q7a86d8afidf6159dcbd35ade@mail.gmail.com>
<62e0109f1003170525m6e187b32n58850bd48259daa8@mail.gmail.com>
<27653.1268835046@sss.pgh.pa.us>
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
Attachments:Mark_Fenbers.vcf (text/x-vcard, 400B) download
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Mark.Fenbers@noaa.gov
Subject: Re: Simple aggregate query brain fart
In-Reply-To: <4BA24184.7060603@noaa.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox