List:General Discussion« Previous MessageNext Message »
From:ViSolve DB Team Date:December 6 2006 10:48am
Subject:Re: SUM() of 1 and NULL is 1 ?
View as plain text  
Hi Vegelin,

In MySQL,

SUM(1) = 1
SUM(NULL) = NULL

In your case, you are inserting two values 1, NULL to Jan column, So now Jan 
column contains 1 and NULL, If you SUM the Jan then the result will be 1 not 
NULL. Because SUM() ignores NULL values.

As far as i know, we dont have built-in function that can be used in SUM() 
to convert values into NULL. We have lot of function to covert NULL into 
values. To get a NULL value instead on 1, you have to write a stored 
procedure or function with the logic given below.

If Column jan contains NULL then return NULL
ELSE return SUM(jan)

Thanks,
ViSolve DB Team



----- Original Message ----- 
From: "C.R.Vegelin" <cr.vegelin@stripped>
To: <mysql@stripped>
Sent: Wednesday, December 06, 2006 3:27 PM
Subject: SUM() of 1 and NULL is 1 ?


Hi List,

I need to SUM() on months from a table like:
CREATE TABLE `data`
( `Jan` float default NULL,
...
) ENGINE=MyISAM; # V 5.0.15

Months may have NULL values, like:
INSERT INTO data (Jan) VALUES (1), (NULL);

However, when I use SELECT SUM(Jan) AS Jan,
the returned value is 1 in stead of NULL.
How to get a result NULL when in such a case ?

TIA, Cor


--------------------------------------------------------------------------------


No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.15.9/573 - Release Date: 12/5/2006

Thread
SUM() of 1 and NULL is 1 ?C.R.Vegelin6 Dec
  • RE: SUM() of 1 and NULL is 1 ?Peter Lauri6 Dec
  • Re: SUM() of 1 and NULL is 1 ?ViSolve DB Team6 Dec
  • Re: SUM() of 1 and NULL is 1 ?ViSolve DB Team6 Dec
  • Re: SUM() of 1 and NULL is 1 ?Remo Tex7 Dec
Re: SUM() of 1 and NULL is 1 ?C.R.Vegelin6 Dec
  • Re: SUM() of 1 and NULL is 1 ?Martijn Tonies6 Dec
  • Re: SUM() of 1 and NULL is 1 ?Peter Brawley6 Dec