Re: [HACKERS] Using aggregate in HAVING

> Bruce Momjian wrote:
> >
> > How would I get all friends greater than the average age?
> >
> > CREATE TABLE friends (
> > firstname CHAR(15),
> > lastname CHAR(20),
> > age INTEGER)
> >
> > SELECT firstname, lastname
> > FROM friends
> > HAVING age >= AVG(age)
> >
> > ERROR: Attribute friends.firstname must be GROUPed or used in an
> > aggregate function
> >
> > This fails too:
> >
> > SELECT firstname, lastname
> > FROM friends
> > WHERE age >= AVG(age)
> >
> > ERROR: Aggregates not allowed in WHERE clause
> >
> > This fails. I am stumped.
>
> Without using subselects? With subselects you could also do:
>
> SELECT firstname, lastname
> FROM friends
> WHERE age >= (SELECT AVG(age) FROM friends);
>
> Are you writing the chapter on aggregates?

I have finished the aggregate chapter, and am doing the subselect
chapter. I thought using a subselect for this example would be great,
but then I thought, "Gee, why can't HAVING do that?" However, I am
realizing that HAVING can't because without a GROUP BY, it applies to
all rows as a whole, and there is no meaningful GROUP BY for this case.

My subquery figure actually will show how HAVING fails, and how
subqueries allow this. Now, I am just asking for confirmation that this
is true.

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026