SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 05:21 AM
| ||||
| ||||
 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      |
|
02-28-2008, 05:21 AM
| |||
| |||
| 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 |
|
02-28-2008, 05:21 AM
| ||||
| ||||
| 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 |
Linear Mode
