List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 31 2002 5:19am
Subject:Re: sum() autoconvert null to zero
View as plain text  
At 6:23 +0200 3/31/02, pascal barbedor wrote:
>----- Original Message -----
>From: pascal barbedor
>To: mysql@stripped
>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.
Thread
sum() autoconvert null to zeropascal barbedor31 Mar
  • Re: sum() autoconvert null to zeroPaul DuBois31 Mar
  • sum() autoconvert null to zeropascal barbedor31 Mar