List:General Discussion« Previous MessageNext Message »
From:Remo Tex Date:December 7 2006 6:34am
Subject:Re: SUM() of 1 and NULL is 1 ?
View as plain text  
C.R.Vegelin wrote:
> 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

So you want NULL if there's 1 ore more NULLs in that column. This could 
be done either in code with separate query/queries or with single SQL 
statement like this:

1. if you don't have 0 i.e. just NULL or 1 in Jan then:
SELECT IF( SUM( COALESCE(Jan,1) ) = SUM(Jan), SUM(Jan), NULL ) AS Jans 
FROM data [GROUP BY ...]

2. if you have 0 then it's more elaborate :-)
SELECT IF( SUM( IF(Jan IS NULL, 1, 0) ) > 0, NULL, SUM(Jan) ) AS Jans 
FROM data [GROUP BY ...]

HTH
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