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@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026