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 <sdavis2@mail.nih.gov>
To: Rick Schumeyer <rschumeyer@ieee.org>, <pgsql-sql@postgresql.org>
Message-ID: <BF713138.10317%sdavis2@mail.nih.gov>
In-Reply-To: <000b01c5ce62$514db090$0300a8c0@dell8200>
Mime-version: 1.0
Content-type: text/plain; charset="US-ASCII"
Content-transfer-encoding: 7bit

On 10/11/05 8:50 AM, "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?

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