pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSimple aggregate query brain fart
4+ messages / 3 participants
[nested] [flat]
* Simple aggregate query brain fart
@ 2010-03-18 15:06 Mark Fenbers <Mark.Fenbers@noaa.gov>
0 siblings, 2 replies; 4+ messages in thread
From: Mark Fenbers @ 2010-03-18 15:06 UTC (permalink / raw)
To: pgsql-sql
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
* Re: Simple aggregate query brain fart
@ 2010-03-18 15:10 Plugge, Joe R. <JRPlugge@west.com>
parent: Mark Fenbers <Mark.Fenbers@noaa.gov>
1 sibling, 0 replies; 4+ messages in thread
From: Plugge, Joe R. @ 2010-03-18 15:10 UTC (permalink / raw)
To: Mark Fenbers <Mark.Fenbers@noaa.gov>; pgsql-sql
Mark,
Change your query to this:
SELECT id, count(*) FROM mytable GROUP BY id HAVING count(*) > 2;
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Mark Fenbers
Sent: Thursday, March 18, 2010 10:07 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Simple aggregate query brain fart
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Simple aggregate query brain fart
@ 2010-03-18 15:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Fenbers <Mark.Fenbers@noaa.gov>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2010-03-18 15:11 UTC (permalink / raw)
To: Mark Fenbers <Mark.Fenbers@noaa.gov>; +Cc: pgsql-sql
Mark Fenbers <Mark.Fenbers@noaa.gov> writes:
> 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.
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Simple aggregate query brain fart
@ 2010-03-18 15:15 Mark Fenbers <Mark.Fenbers@noaa.gov>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Mark Fenbers @ 2010-03-18 15:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
end of thread, other threads:[~2010-03-18 15:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-18 15:06 Simple aggregate query brain fart Mark Fenbers <Mark.Fenbers@noaa.gov>
2010-03-18 15:10 ` Plugge, Joe R. <JRPlugge@west.com>
2010-03-18 15:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-18 15:15 ` Mark Fenbers <Mark.Fenbers@noaa.gov>
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