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
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
