Re: Question about SUM and Nulls

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 02/23/05 

Date: Wed, 23 Feb 2005 23:41:05 +0100

On Wed, 23 Feb 2005 10:46:30 -0500, Michael C# wrote:

(snip)
>I find it strange that the geniuses who come up with this stuff decided that
>when performing a scalar operation with a NULL value the NULL cannot be
>treated as zero;

Hi Michael,

That's logical, because NULL is meant to be a marker for missing data.
Why the data is missing (unknown, not applicable, not relevant, ...) is
not important - to the database, the data is missing and the reason why
it's missing is not known or relevant.

Suppose I asked you what our age difference is, in other words: the
result of the calculation (your age) - (my age). For you, my age is not
known - it's NULL. Would you treat is as zero, subtract zero from your
age and post that as our age difference?

Oh, and would you also want NULL to be treated as zero in scalar
expressions involving multiplication? Or division?

> but when performing it in an aggregate operation NULLs can
>be ignored, effectively treating them as zero.

This statement is not correct: ignoring NULLS is *NOT* effective
treating them as zero. It might look that way, since SUM {1, 2} is the
same as SUM {0, 1, 2}. But AVG {1, 2} is not the same as AVG {0, 1, 2},
COUNT {1, 2} is not the same as COUNT {0, 1, 2} and MIN {1, 2} is quite
different from MIN {0, 1, 2}.

Anyway, ignoring NULLs makes perfect sense in aggregates, since these
are *defined* as the sum (or average, number, minimum, or maximum) of
all non-missing values in the set.

Of course, the SQL committee might have chosen to define the result of
any aggregate including a NULL in it's set as NULL. I don't see any
advantage of such a decision. But I do see how that would render these
aggregate functions almost useless.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: To John Harshman -- re Isochrons
    ... The reason radiometric dating fails on recent rocks is that you'll get ... do you actually get an age of zero because radiometric dating has been ... method on recent rock? ...
    (talk.origins)
  • Re: Need modern version of old technique to show missing values
    ... The above-mentioned R has the symbol "NA" to indicate missing values, ... distinguished from the usual IEEE754 floating point special values ... negative zero. ... seem determined to use an older standard this option is of course not ...
    (comp.lang.fortran)
  • Re: To John Harshman -- re Isochrons
    ... Contorted bedding provides evidence of c. 15 m of crystal ... premise of zero age at solidification. ... the rock is zero years old at solidification, ...
    (talk.origins)
  • Re: 3vl 2vl and NULL
    ... >>>someone whose age (in this database) is less than or equal to Marge's ... >Uncle Henry's age is unknown ... the handling of missing data changes as well. ...
    (comp.databases.theory)
  • Re: Need modern version of old technique to show missing values
    ... pages and allows selection of any program out a class, ... CSV style ASCII files that are human readable, where "missing" values ... zero for a real value of zero and a negative real zero to indicate ... data with this interior coding. ...
    (comp.lang.fortran)