List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:December 6 2006 5:39pm
Subject:Re: SUM() of 1 and NULL is 1 ?
View as plain text  
Cor,

>This is a serious matter, because:
>- within a row: 1 + NULL = NULL
>- across rows with SUM(): 1 + NULL = 1
>I know the manual says that group functions ignore 
>NULL values (12.10.1), but it also says: Conceptually, 
>NULL means "a missing unknown value" (3.3.4.6).
>IMHO a NULL with any value should always add to NULL.

As Martijn says, it's correct for the ISO SQL standard. But you're right, it is
inconsistent. For very long & sometimes bilious lists of such inconsistencies see the
writings of Codd, Date and Pascal. This particular inconsistency agrees with common
statistical practice for aggregates--omit missing values rather than abandon the
computation. To get a count of missing values select SUM(IF(ISNULL(col_name),1,0)).

PB

-----

C.R.Vegelin wrote:
> Thanks Visolve, Peter,
>
> This is a serious matter, because:
> - within a row: 1 + NULL = NULL
> - across rows with SUM(): 1 + NULL = 1
>
> I know the manual says that group functions ignore NULL values (12.10.1), 
> but it also says: Conceptually, NULL means "a missing unknown value" (3.3.4.6).
> IMHO a NULL with any value should always add to NULL.
> I was hoping for an option / setting to change NULL behaviour.
> Well, I will try the suggested alternatives.
>
> Thanks, Cor
>   
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.409 / Virus Database: 268.15.9/571 - Release Date: 12/5/2006
>   

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.11/575 - Release Date: 12/6/2006
Thread
SUM() of 1 and NULL is 1 ?C.R.Vegelin6 Dec
  • RE: SUM() of 1 and NULL is 1 ?Peter Lauri6 Dec
  • Re: SUM() of 1 and NULL is 1 ?ViSolve DB Team6 Dec
  • Re: SUM() of 1 and NULL is 1 ?ViSolve DB Team6 Dec
  • Re: SUM() of 1 and NULL is 1 ?Remo Tex7 Dec
Re: SUM() of 1 and NULL is 1 ?C.R.Vegelin6 Dec
  • Re: SUM() of 1 and NULL is 1 ?Martijn Tonies6 Dec
  • Re: SUM() of 1 and NULL is 1 ?Peter Brawley6 Dec