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.

-- 
  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, Pennsylvania 19026

Wed, 29 Dec 1999 14:51:19 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
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

Wed, 29 Dec 1999 15:17:28 -0500
Mike Mascari <mascarm@mascari.com>
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, Pennsylvania 19026

Wed, 29 Dec 1999 15:25:27 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
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

Wed, 29 Dec 1999 15:57:44 -0500
Mike Mascari <mascarm@mascari.com>
Re: [HACKERS] Using aggregate in HAVING