SUM() of 1 and NULL is 1 ?

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 is 1 in stead of

RE: SUM() of 1 and NULL is 1 ?

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

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 not NULL. Because SUM() ignores NULL values. As far as i know, we dont have built

Re: SUM() of 1 and NULL is 1 ?

] 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

Re: SUM() of 1 and NULL is 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

Re: SUM() of 1 and NULL is 1 ?

://www.databasedevelopmentforum.com 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

Re: SUM() of 1 and NULL is 1 ?

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

Re: SUM() of 1 and NULL is 1 ?

then: SELECT IF( SUM( COALESCE(Jan,1) ) = SUM(Jan), SUM(Jan), NULL ) AS Jans FROM data [GROUP BY ...] 2. if you have 0 then it's more elaborate :-) SELECT IF( SUM( IF(Jan IS NULL, 1, 0) ) 0, NULL, SUM(Jan) ) AS Jans FROM data [GROUP BY ...] HTH -- MySQL General Mailing List For list archives