Re: [HACKERS] Using aggregate in HAVING

Bruce Momjian wrote:
> 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.
> 

Well...actually, you can use a self-join like so:

SELECT f1.lastname, f1.firstname, f1.age, avg(f2.age)
FROM friends f1, friends f2
WHERE true
GROUP BY f1.lastname, f1.firstname, f1.age
HAVING f1.age > avg(f2.age);

I don't think you'll be able to state that subselects allow for
queries that HAVING won't. Proving a negative can be very
difficult (although I think you're probably right).

Mike Mascari