IF(SUM(IF(Jan IS NULL, 0, Jan))0, SUM(IF(Jan IS NULL, 0, Jan)), NULL)
This was just a guess :)
-Original Message-
From: C.R.Vegelin [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 11:58 AM
To: mysql@lists.mysql.com
Subject: SUM() of 1 and NULL is 1 ?
Hi List,
I need to
Hi Vegelin,
In MySQL,
SUM(1) = 1
SUM(NULL) = NULL
In your case, you are inserting two values 1, NULL to Jan column, So now Jan
column contains 1 and NULL, If you SUM the Jan then the result will be 1 not
NULL. Because SUM() ignores NULL values.
As far as i know, we dont have built-in
]
Sent: Wednesday, December 06, 2006 4:18 PM
Subject: Re: SUM() of 1 and NULL is 1 ?
Hi Vegelin,
In MySQL,
SUM(1) = 1
SUM(NULL) = NULL
In your case, you are inserting two values 1, NULL to Jan column, So now
Jan column contains 1 and NULL, If you SUM the Jan then the result will be
1
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
Aggregates ignore NULL as per SQL standard, so this behaviour
is valid.
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
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
C.R.Vegelin wrote:
Hi List,
I need to SUM() on months from a table like:
CREATE TABLE `data`
( `Jan` float default NULL,
...
) ENGINE=MyISAM; # V 5.0.15
Months may have NULL values, like:
INSERT INTO data (Jan) VALUES (1), (NULL);
However, when I use SELECT SUM(Jan) AS Jan,
the returned value
