At 6:23 +0200 3/31/02, pascal barbedor wrote:
>----- Original Message -----
>From: pascal barbedor
>Sent: Sunday, March 31, 2002 6:19 AM
>Subject: sum() autoconvert null to zero
>>Null is an unidentified value, but MySQL will store 0 or ''. You can read
>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')
>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
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.