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 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 NULL. How to get a result NULL when in such a case ? TIA, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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-in function that can be used in SUM() to convert values into NULL. We have lot of function to covert NULL into values. To get a NULL value instead on 1, you have to write a stored procedure or function with the logic given below. If Column jan contains NULL then return NULL ELSE return SUM(jan) Thanks, ViSolve DB Team - Original Message - From: C.R.Vegelin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, December 06, 2006 3:27 PM Subject: 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 NULL. How to get a result NULL when in such a case ? TIA, Cor No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.9/573 - Release Date: 12/5/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SUM() of 1 and NULL is 1 ?
Hi Vegelin, This will do.. select IF(SUM(IF(Jan IS NULL, 0, Jan))0,NULL,SUM(IF(Jan IS NULL, 0, Jan)))as jan from data; Thanks, ViSolve DB Team - Original Message - From: ViSolve DB Team [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED]; mysql@lists.mysql.com Cc: [EMAIL PROTECTED] 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 not NULL. Because SUM() ignores NULL values. As far as i know, we dont have built-in function that can be used in SUM() to convert values into NULL. We have lot of function to covert NULL into values. To get a NULL value instead on 1, you have to write a stored procedure or function with the logic given below. If Column jan contains NULL then return NULL ELSE return SUM(jan) Thanks, ViSolve DB Team - Original Message - From: C.R.Vegelin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, December 06, 2006 3:27 PM Subject: 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 NULL. How to get a result NULL when in such a case ? TIA, Cor No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.9/573 - Release Date: 12/5/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.9/573 - Release Date: 12/5/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 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
Re: SUM() of 1 and NULL is 1 ?
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! http://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 (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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 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]
Re: SUM() of 1 and NULL is 1 ?
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 is 1 in stead of NULL. How to get a result NULL when in such a case ? TIA, Cor So you want NULL if there's 1 ore more NULLs in that column. This could be done either in code with separate query/queries or with single SQL statement like this: 1. if you don't have 0 i.e. just NULL or 1 in Jan 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: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
