Thread: Using aggregate in HAVING

How would I get all friends greater than the average age?
CREATE TABLE friends (     firstname CHAR(15),     lastname CHAR(20),     age INTEGER)
SELECT firstname, lastnameFROM friendsHAVING age >= AVG(age)ERROR:  Attribute friends.firstname must be GROUPed or used
inanaggregate function
 

This fails too:
SELECT firstname, lastnameFROM friendsWHERE age >= AVG(age)ERROR:  Aggregates not allowed in WHERE clause

This fails.  I am stumped.

--  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
 

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

> 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
 

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