| From: | "Rick Schumeyer" <rschumeyer(at)ieee(dot)org> |
|---|---|
| To: | <pgsql-sql(at)postgresql(dot)org> |
| Subject: | question re. count, group by, and having |
| Date: | 2005-10-11 12:50:10 |
| Message-ID: | 000b01c5ce62$514db090$0300a8c0@dell8200 |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-sql |
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).
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Sean Davis | 2005-10-11 13:07:04 | Re: question re. count, group by, and having |
| Previous Message | Richard Huxton | 2005-10-11 12:29:54 | Re: SEVEN cross joins?!?!? |