From: Peter Brawley
Date: December 6 2006 5:39pm
Subject: Re: SUM() of 1 and NULL is 1 ?
List-Archive: http://lists.mysql.com/mysql/203738
Message-Id: <45770044.4000908@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=======AVGMAIL-457700457A66======="
--=======AVGMAIL-457700457A66=======
Content-Type: multipart/alternative; boundary=------------050109070707000009020904
--------------050109070707000009020904
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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
>
--------------050109070707000009020904
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
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
--------------050109070707000009020904--
--=======AVGMAIL-457700457A66=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"
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
--=======AVGMAIL-457700457A66=======--