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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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 <contact2muraliin@yahoo.co.in>
Subject: Re: question re. count, group by, and having
To: Rick Schumeyer <rschumeyer@ieee.org>, 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 <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
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

<DIV>Hi</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp; Logically HAVING is executed after the GROUP BY and it must contain only the columns in the GROUP BY or aggregated function. </DIV>
<DIV>&nbsp;</DIV>
<DIV><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">select state, count(state) as c from t group by state having c &gt; 5 </SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN>&nbsp;</DIV>
<DIV><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">The above query grouped only on state and HAVING can be used only with the column state or the aggregated function count(state).</SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN>&nbsp;</DIV>
<DIV><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Regards,</SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">R.Muralidharan</SPAN></DIV>
<DIV>&nbsp; <BR><BR><B><I>Rick Schumeyer &lt;rschumeyer@ieee.org&gt;</I></B> wrote:</DIV>
<BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">
<META content="Microsoft Word 10 (filtered)" name=Generator>
<STYLE>
<!--
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{font-family:Arial;
	color:windowtext;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}
-->
</STYLE>

<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">The following query returns an error (“column c does not exist”) in pg 8.0.3:</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT>&nbsp;</P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">(The column ‘state’ is the two letter abbreviation for a </SPAN></FONT><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">US</SPAN></FONT><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> state)</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT>&nbsp;</P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">-- get the number of rows for each state; list in descending order; include only states with at least 6 rows</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">select state, count(state) as c from t group by state having c &gt; 5 order by c desc; -- gives error</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT>&nbsp;</P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">If I leave the having clause out, I get the expected results:</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT>&nbsp;</P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">select state, count(state) as c from t group by state order by c desc; -- this works</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT>&nbsp;</P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Is this a bug or a feature?&nbsp; I’m not sure why I can use ‘c’ in the order by clause but not the having clause.&nbsp; pg is much happier with the full “having count(state) &gt; 5”.&nbsp; Will this cause count to be evaluated twice?</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT>&nbsp;</P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">If it matters, state is varchar(2).</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT>&nbsp;</P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT>&nbsp;</P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT>&nbsp;</P></DIV></BLOCKQUOTE><p>
		<hr size=1> 
<a href=http://yahoo.shaadi.com>Yahoo! India Matrimony</a href>: Find your partner online.
--0-555375571-1129066068=:41045--