From: | Sean Davis <sdavis2(at)mail(dot)nih(dot)gov> |
---|---|
To: | Rick Schumeyer <rschumeyer(at)ieee(dot)org>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: question re. count, group by, and having |
Date: | 2005-10-11 13:07:04 |
Message-ID: | BF713138.10317%sdavis2@mail.nih.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 10/11/05 8:50 AM, "Rick Schumeyer" <rschumeyer(at)ieee(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Neil Saunders | 2005-10-11 13:20:16 | Difference from average |
Previous Message | Rick Schumeyer | 2005-10-11 12:50:10 | question re. count, group by, and having |