From: Muralidharan Ramakrishnan <contact2muraliin@yahoo.co.in>
To: Rick Schumeyer <rschumeyer@ieee.org>
To: pgsql-sql@postgresql.org
Subject: Re: question re. count, group by, and having
Date: Tue, 11 Oct 2005 22:27:48 +0100 (BST)
Message-ID: <20051011212748.43240.qmail@web8606.mail.in.yahoo.com> (raw)
In-Reply-To: <000b01c5ce62$514db090$0300a8c0@dell8200>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: contact2muraliin@yahoo.co.in, rschumeyer@ieee.org
Subject: Re: question re. count, group by, and having
In-Reply-To: <20051011212748.43240.qmail@web8606.mail.in.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox