Hi thanks for answer,
maybe it would be good to have a sum_null() function to deal with my case ? because logically speaking, when a value is null in an operation the result is null, this is the case for normal functions in mysql (ie not "group by" function) i think it is perfect sense that count() return 0 if only null values, but for sum(), since there is a arithmetical operation, it is indetermined as soon as one row is indetermined. else how can I make the difference between values that sum to zero ( 0,0 ; or -2, 2) and a collection of null values ? > > 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. then what is the best solution for my case ? if I want a result as if sum() would result null each time only one row contains null ? > >field N G > >row 1 2 a > >row 2 3 a > >row 3 null b > > i have to do maybe select avg(N)*count(N), g from t group by g to get a 5 b null thanks --------------------------------------------------------------------- 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
