Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!ames!oliveb!apple!voder!cullsj!gupta
From: gupta@cullsj.UUCP (Yogesh Gupta)
Newsgroups: comp.databases
Subject: Re: Nesting SQL Commands?
Summary: Do you need them??
Message-ID: <553@cullsj.UUCP>
Date: 26 Apr 89 02:46:50 GMT
References: <4YGLTiy00VA-MCvmVY@andrew.cmu.edu> <421@cimshop.UUCP>
Organization: Cullinet Software, San Jose, CA
Lines: 32

In article <421@cimshop.UUCP>, dsm@cimshop.UUCP (David Masterson) writes:
> In message <4YGLTiy00VA-MCvmVY@andrew.cmu.edu>, dp22+@andrew.cmu.edu writes:
> >     Is there an implementation of SQL around that will allow functions
> >to be nested in the same where clause?
> >i.e.
> >
> >   WHERE    AVG(Col1 + Col2) = MIN(AVG(Col1+Col2))
> >
> What is the MINimum of an AVeraGe?  An average is only one number just like
> a minimum is.  An aggregate function takes as input a set of numbers and 
> produces a number as output.  Therefore, nesting two aggregate functions
> like above doesn't make sense, but does the following?
> 
> 	WHERE MIN(col1) > MIN(col2 + AVG(col1))

I do not think why one needs to write the above as a nested function.
	MIN(col2 + AVG(col1)) == MIN(col2) + AVG(col1)
Since an aggregate function returns a single value, it is a constant in
an expression that is inside another function, and can be taken out of
the expression.  Also, since MIN, MAX, SUM, and AVG of a constant are
the same constant, the outer function can be eliminated:

	MIN(col2 + AVG(col1)) == MIN(col2) + MIN(AVG(col1))     (1)
			      == MIN(col2) + AVG(col1)          (2)

	(1) is due to AVG(col1) being a constant, and
	(2) is due to the fact that the MIN of a constant is that constant.

Anyway, I guess this has been belaboured enough.
-- 
Yogesh Gupta                    | If you think my company will let me
Cullinet Software, Inc.         | speak for them, you must be joking.