Re: does a table always need a PK?

"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message news:Vuo5b.11$eu1.202_at_news.oracle.com...
>
> "Lee Fesperman" <firstsql_at_ix.netcom.com> wrote in message
> news:3F559DC5.7E91_at_ix.netcom.com...
> > Mikito Harakiri wrote:
> > > AVG = SUM(val)/SUM(1) is redundant
> >
> > That's ugly. And, it is incorrect. I see no problem with shorthands like
> AVG().
>
> Ugly or not, that's a mathematical definition of what AVG is. Make it AVG
=
> SUM(val)/COUNT(1), if you want to correct it for nulls. If the definition
> doesn't work for extreme cases like zero rows, it also means that it
should
> be redefined to suit those, as they routinly do in math.

The definition works for zero rows where average is undefined. The problem is NULL. The proper definition accounting for NULL is:

AVG(val) = SUM(val)/COUNT(val)

or

AVG(val) = SUM(val)/SUM( val = val )

The latter definition actually matches SQL better because one could argue the first definition should result in an exception for an empty table while the second definition without question results in NULL for an empty table. Received on Wed Sep 03 2003 - 18:24:44 CEST