At 6:23 +0200 3/31/02, pascal barbedor wrote: >----- Original Message ----- >From: pascal barbedor >To: [EMAIL PROTECTED] >Sent: Sunday, March 31, 2002 6:19 AM >Subject: sum() autoconvert null to zero > > > Egor, > >>Null is an unidentified value, but MySQL will store 0 or ''. You can read >about >>it at: >> http://www.mysql.com/doc/B/u/Bugs.html > > >hi thanks for answer, > >but the page you mention only talk about converting null to zero if null >value not allowed in a column : > > >If you try to store NULL into a column that doesn't take NULL values, MySQL >Server will store 0 or '' (empty string) in it instead. (This behavior can, >however, be changed with the -DDONT_USE_DEFAULT_FIELDS compile option). > >in my case, null values ARE allowed in the column, but if i sum(col), it >will be as if null values converted to zero. > >for instance : > >create table t (N short null, G char(1)) >insert into t values (2,'a') >insert into t values (3,'a') >insert into t values (null,'b') >resul in: >field N G >row 1 2 a >row 2 3 a >row 3 null b > > >and then select sum(N) from t gives 5, instead of null. > >and select sum(N), G from t group by G gives >a 5 >b 0 > >instead of >a 5 >b null > >as expected. > >thanks
SUM() adds only the non-NULL values in a group. So the sum of a group containing only NULL values is zero. It's not "converting" NULL values to zero, it's ignoring them. Makes perfect sense. Now, AVG(), on the other hand would give you a result of NULL, because AVG() is the ratio of the sum of the non-NULL values to the number of non-NULL values. For a group containing only NULL, that's 0/0, which is undefined, which is represented by NULL. It would make sense for SUM() to be NULL if you thought of it as adding together NULL values, but if SUM() really did that, then you'd get a NULL sum for any group that contained even one NULL value. And that would really be a pain, because then to get a count of the non-NULL values you'd have to write SUM(IF(col IS NULL,0,col)) everywhere you wanted a sum. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
