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? 

Mike Mascari