question re. count, group by, and having

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: Raw Message | Whole Thread | 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).

Responses

Browse pgsql-sql by date

  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?!?!?