From rschumeyer@ieee.org Tue Oct 11 12:50:11 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E7BB8D708C for ; Tue, 11 Oct 2005 09:50:11 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 47322-03 for ; Tue, 11 Oct 2005 12:50:08 +0000 (GMT) Received: from sccrmhc14.comcast.net (sccrmhc14.comcast.net [63.240.76.49]) by svr1.postgresql.org (Postfix) with ESMTP id 3056BD706B for ; Tue, 11 Oct 2005 09:50:09 -0300 (ADT) Received: from dell8200 (pcp09613107pcs.rte20201.de.comcast.net[68.82.197.68]) by comcast.net (sccrmhc14) with SMTP id <2005101112501201400oolq6e>; Tue, 11 Oct 2005 12:50:13 +0000 From: "Rick Schumeyer" To: Subject: question re. count, group by, and having Date: Tue, 11 Oct 2005 08:50:10 -0400 Message-ID: <000b01c5ce62$514db090$0300a8c0@dell8200> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000C_01C5CE40.CA3C1090" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200510/84 X-Sequence-Number: 22937 This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C5CE40.CA3C1090 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit The following query returns an error ("column c does not exist") in pg 8.0.3: (The column 'state' is the two letter abbreviation for a US state) -- get the number of rows for each state; list in descending order; include only states with at least 6 rows select state, count(state) as c from t group by state having c > 5 order by c desc; -- gives error If I leave the having clause out, I get the expected results: select state, count(state) as c from t group by state order by c desc; -- this works Is this a bug or a feature? I'm not sure why I can use 'c' in the order by clause but not the having clause. pg is much happier with the full "having count(state) > 5". Will this cause count to be evaluated twice? If it matters, state is varchar(2). ------=_NextPart_000_000C_01C5CE40.CA3C1090 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

The following query returns an error (“column c = does not exist”) in pg 8.0.3:

(The column ‘state’ is the two letter abbreviation for a US = state)

-- get the number of rows for each state; list in = descending order; include only states with at least 6 rows

select state, count(state) as c from t group by state = having c > 5 order by c desc; -- gives error

If I leave the having clause out, I get the expected results:

select state, count(state) as c from t group by state = order by c desc; -- this works

Is this a bug or a feature? I’m not sure = why I can use ‘c’ in the order by clause but not the having = clause. pg is much happier with the full “having count(state) > = 5”. Will this cause count to be evaluated twice?

If it matters, state is varchar(2).

------=_NextPart_000_000C_01C5CE40.CA3C1090-- From sdavis2@mail.nih.gov Tue Oct 11 13:07:04 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C91BAD6F01 for ; Tue, 11 Oct 2005 10:07:04 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 99559-04 for ; Tue, 11 Oct 2005 13:07:00 +0000 (GMT) Received: from wimpy.net.nih.gov (mailfwd.nih.gov [128.231.88.106]) by svr1.postgresql.org (Postfix) with ESMTP id BCBA0D6E41 for ; Tue, 11 Oct 2005 10:07:02 -0300 (ADT) Received: from wimpy.net.nih.gov (localhost [127.0.0.1]) by wimpy.net.nih.gov (8.12.11/8.11.7) with ESMTP id j9BD75vn011071 for ; Tue, 11 Oct 2005 09:07:06 -0400 (EDT) Received: from [128.231.145.14] (holmes.nhgri.nih.gov [128.231.145.14]) by wimpy.net.nih.gov (8.12.11/8.11.7) with ESMTP id j9BD75E2011068; Tue, 11 Oct 2005 09:07:05 -0400 (EDT) User-Agent: Microsoft-Entourage/10.1.4.030702.0 Date: Tue, 11 Oct 2005 09:07:04 -0400 Subject: Re: question re. count, group by, and having From: Sean Davis To: Rick Schumeyer , Message-ID: In-Reply-To: <000b01c5ce62$514db090$0300a8c0@dell8200> Mime-version: 1.0 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.013 required=5 tests=[AWL=0.013] X-Spam-Level: X-Archive-Number: 200510/85 X-Sequence-Number: 22938 On 10/11/05 8:50 AM, "Rick Schumeyer" wrote: > The following query returns an error ("column c does not exist") in pg 8.0.3: > > > > (The column 'state' is the two letter abbreviation for a US state) > > > > -- get the number of rows for each state; list in descending order; include > only states with at least 6 rows > > select state, count(state) as c from t group by state having c > 5 order by c > desc; -- gives error > > > > If I leave the having clause out, I get the expected results: > > > > select state, count(state) as c from t group by state order by c desc; -- this > works > > > > Is this a bug or a feature? I'm not sure why I can use 'c' in the order by > clause but not the having clause. pg is much happier with the full "having > count(state) > 5". Will this cause count to be evaluated twice? I think that postgres is smart enough to do the evaluation only once, but this might be version-dependent, but one of the gurus will have to comment on which version (if there is a version dependence) first made this improvement. Sean From tgl@sss.pgh.pa.us Tue Oct 11 14:04:02 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6F9D2D6D71 for ; Tue, 11 Oct 2005 11:04:02 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 48765-04 for ; Tue, 11 Oct 2005 14:03:57 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 9D124D711F for ; Tue, 11 Oct 2005 11:03:59 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j9BE3wQ7013794; Tue, 11 Oct 2005 10:04:00 -0400 (EDT) To: "Rick Schumeyer" Cc: pgsql-sql@postgresql.org Subject: Re: question re. count, group by, and having In-reply-to: <000b01c5ce62$514db090$0300a8c0@dell8200> References: <000b01c5ce62$514db090$0300a8c0@dell8200> Comments: In-reply-to "Rick Schumeyer" message dated "Tue, 11 Oct 2005 08:50:10 -0400" Date: Tue, 11 Oct 2005 10:03:58 -0400 Message-ID: <13793.1129039438@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200510/87 X-Sequence-Number: 22940 "Rick Schumeyer" writes: > Is this a bug or a feature? I'm not sure why I can use 'c' in the order by > clause but not the having clause. pg is much happier with the full "having > count(state) > 5". Actually, referring to any of the output columns in any of the modifier clauses is logically suspect. Original SQL (back around 89 or so) required ORDER BY items to be output column names, thus wiring in an assumption that sorting happens after calculation of the output values, but that is surely not true for any of the other clauses. And it's pretty bogus even for sorting, since you might wish to sort on a value you're not displaying. If we were working in a green field we'd doubtless get rid of the output-column-reference feature entirely. But for backward compatibility's sake we're stuck with allowing ORDER BY items to be simple output column names, per ancient versions of the SQL spec. At one point or another somebody thought it a good idea to propagate that special rule into GROUP BY; which in hindsight was an awful idea. (It's not in the spec. I'm not sure if this is just a Postgres-ism or if we borrowed someone else's bad idea.) But we're stuck with supporting that odd case too, now. We certainly aren't going to add more. > Will this cause count to be evaluated twice? Recent versions of PG are smart enough to merge duplicate aggregates. This isn't necessarily true for other forms of common subexpressions, but it works for aggregate functions. regards, tom lane From contact2muraliin@yahoo.co.in Tue Oct 11 21:27:52 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AD6B0D7725 for ; Tue, 11 Oct 2005 18:27:52 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 67938-10 for ; Tue, 11 Oct 2005 21:27:48 +0000 (GMT) Received: from web8606.mail.in.yahoo.com (web8606.mail.in.yahoo.com [202.43.219.81]) by svr1.postgresql.org (Postfix) with SMTP id 297D5D771F for ; Tue, 11 Oct 2005 18:27:48 -0300 (ADT) Received: (qmail 43242 invoked by uid 60001); 11 Oct 2005 21:27:48 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.in; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=UL+cCIaOi+WvJkjHXAl3RBIPcW0COSCMZi5WdzJDz1kpMX5B2g1sxxysFuNKD6A09wLTgEr+MmPY8eY/iKEeZYRg66n0xHXiNVBeLBaIjt4cPfEWMp0ANuvQvD1ybuQHBKODZq39+xspFHu3448zXENyVI/FDlLL8rHVaDL9aCY= ; Message-ID: <20051011212748.43240.qmail@web8606.mail.in.yahoo.com> Received: from [12.109.150.2] by web8606.mail.in.yahoo.com via HTTP; Tue, 11 Oct 2005 22:27:48 BST Date: Tue, 11 Oct 2005 22:27:48 +0100 (BST) From: Muralidharan Ramakrishnan Subject: Re: question re. count, group by, and having To: Rick Schumeyer , pgsql-sql@postgresql.org In-Reply-To: <000b01c5ce62$514db090$0300a8c0@dell8200> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-555375571-1129066068=:41045" Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.751 required=5 tests=[DNS_FROM_RFC_ABUSE=0.374, DNS_FROM_RFC_POST=1.376, HTML_MESSAGE=0.001] X-Spam-Level: * X-Archive-Number: 200510/98 X-Sequence-Number: 22951 --0-555375571-1129066068=:41045 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi Logically HAVING is executed after the GROUP BY and it must contain only the columns in the GROUP BY or aggregated function. select state, count(state) as c from t group by state having c > 5 The above query grouped only on state and HAVING can be used only with the column state or the aggregated function count(state). Regards, R.Muralidharan Rick Schumeyer wrote: The following query returns an error (“column c does not exist”) in pg 8.0.3: (The column ‘state’ is the two letter abbreviation for a US state) -- get the number of rows for each state; list in descending order; include only states with at least 6 rows select state, count(state) as c from t group by state having c > 5 order by c desc; -- gives error If I leave the having clause out, I get the expected results: select state, count(state) as c from t group by state order by c desc; -- this works Is this a bug or a feature? I’m not sure why I can use ‘c’ in the order by clause but not the having clause. pg is much happier with the full “having count(state) > 5”. Will this cause count to be evaluated twice? If it matters, state is varchar(2). --------------------------------- Yahoo! India Matrimony: Find your partner online. --0-555375571-1129066068=:41045 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit

Logically HAVING is executed after the GROUP BY and it must contain only the columns in the GROUP BY or aggregated function.

select state, count(state) as c from t group by state having c > 5

The above query grouped only on state and HAVING can be used only with the column state or the aggregated function count(state).

Regards,

R.Muralidharan

Rick Schumeyer <rschumeyer@ieee.org> wrote:

The following query returns an error (“column c does not exist”) in pg 8.0.3:

(The column ‘state’ is the two letter abbreviation for a US state)

-- get the number of rows for each state; list in descending order; include only states with at least 6 rows

select state, count(state) as c from t group by state having c > 5 order by c desc; -- gives error

If I leave the having clause out, I get the expected results:

select state, count(state) as c from t group by state order by c desc; -- this works

Is this a bug or a feature? I’m not sure why I can use ‘c’ in the order by clause but not the having clause. pg is much happier with the full “having count(state) > 5”. Will this cause count to be evaluated twice?

If it matters, state is varchar(2).

Yahoo! India Matrimony: Find your partner online. --0-555375571-1129066068=:41045--