From: Mark Fenbers <Mark.Fenbers@noaa.gov>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Simple aggregate query brain fart
Date: Thu, 18 Mar 2010 11:15:55 -0400
Message-ID: <4BA243AB.10804@noaa.gov> (raw)
In-Reply-To: <11074.1268925073@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>
<4BA24184.7060603@noaa.gov>
<11074.1268925073@sss.pgh.pa.us>
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. 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>
<blockquote type="cite">
<pre wrap="">I want to do:
SELECT id, count(*) FROM mytable WHERE count(*) > 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)--
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, tgl@sss.pgh.pa.us
Subject: Re: Simple aggregate query brain fart
In-Reply-To: <4BA243AB.10804@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