pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquestion re. count, group by, and having
4+ messages / 4 participants
[nested] [flat]
* question re. count, group by, and having
@ 2005-10-11 12:50 Rick Schumeyer <rschumeyer@ieee.org>
2005-10-11 13:07 ` Re: question re. count, group by, and having Sean Davis <sdavis2@mail.nih.gov>
2005-10-11 14:03 ` Re: question re. count, group by, and having Tom Lane <tgl@sss.pgh.pa.us>
2005-10-11 21:27 ` Re: question re. count, group by, and having Muralidharan Ramakrishnan <contact2muraliin@yahoo.co.in>
0 siblings, 3 replies; 4+ messages in thread
From: Rick Schumeyer @ 2005-10-11 12:50 UTC (permalink / raw)
To: 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).
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: question re. count, group by, and having
2005-10-11 12:50 question re. count, group by, and having Rick Schumeyer <rschumeyer@ieee.org>
@ 2005-10-11 13:07 ` Sean Davis <sdavis2@mail.nih.gov>
2 siblings, 0 replies; 4+ messages in thread
From: Sean Davis @ 2005-10-11 13:07 UTC (permalink / raw)
To: Rick Schumeyer <rschumeyer@ieee.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: question re. count, group by, and having
2005-10-11 12:50 question re. count, group by, and having Rick Schumeyer <rschumeyer@ieee.org>
@ 2005-10-11 14:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-10-11 14:03 UTC (permalink / raw)
To: Rick Schumeyer <rschumeyer@ieee.org>; +Cc: pgsql-sql
"Rick Schumeyer" <rschumeyer@ieee.org> writes:
> 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".
Actually, referring to any of the output columns in any of the modifier
clauses is logically suspect. Original SQL (back around 89 or so)
required ORDER BY items to be output column names, thus wiring in an
assumption that sorting happens after calculation of the output values,
but that is surely not true for any of the other clauses. And it's
pretty bogus even for sorting, since you might wish to sort on a value
you're not displaying.
If we were working in a green field we'd doubtless get rid of the
output-column-reference feature entirely. But for backward
compatibility's sake we're stuck with allowing ORDER BY items to
be simple output column names, per ancient versions of the SQL spec.
At one point or another somebody thought it a good idea to propagate
that special rule into GROUP BY; which in hindsight was an awful idea.
(It's not in the spec. I'm not sure if this is just a Postgres-ism
or if we borrowed someone else's bad idea.) But we're stuck with
supporting that odd case too, now. We certainly aren't going to add
more.
> Will this cause count to be evaluated twice?
Recent versions of PG are smart enough to merge duplicate aggregates.
This isn't necessarily true for other forms of common subexpressions,
but it works for aggregate functions.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: question re. count, group by, and having
2005-10-11 12:50 question re. count, group by, and having Rick Schumeyer <rschumeyer@ieee.org>
@ 2005-10-11 21:27 ` Muralidharan Ramakrishnan <contact2muraliin@yahoo.co.in>
2 siblings, 0 replies; 4+ messages in thread
From: Muralidharan Ramakrishnan @ 2005-10-11 21:27 UTC (permalink / raw)
To: Rick Schumeyer <rschumeyer@ieee.org>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-10-11 21:27 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-11 12:50 question re. count, group by, and having Rick Schumeyer <rschumeyer@ieee.org>
2005-10-11 13:07 ` Sean Davis <sdavis2@mail.nih.gov>
2005-10-11 14:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-11 21:27 ` Muralidharan Ramakrishnan <contact2muraliin@yahoo.co.in>
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