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