SEO

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

IF(SUM(IF(Jan IS NULL, 0, Jan))>0, SUM(IF(Jan IS NULL, 0, Jan)), NULL)

This was just a guess



-----Original Message-----
From: C.R.Vegelin [mailto:cr.vegelin@hetnet.nl]
Sent: Wednesday, December 06, 2006 11:58 AM
To: mysql@lists.mysql.com
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

Hi Vegelin,

This will do..

select IF(SUM(IF(Jan IS NULL, 0, Jan))>0,NULL,SUM(IF(Jan IS NULL, 0,
Jan)))as jan from data;

Thanks,
ViSolve DB Team

----- Original Message -----
From: "ViSolve DB Team" <mysql_support@visolve.com>
To: "C.R.Vegelin" <cr.vegelin@hetnet.nl>; <mysql@lists.mysql.com>
Cc: <mysql_support@kovaiteam.com>
Sent: Wednesday, December 06, 2006 4:18 PM
Subject: Re: SUM() of 1 and NULL is 1 ?


> 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@hetnet.nl>
> To: <mysql@lists.mysql.com>
> 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
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=m...rt@visolve.com
>
>
>
> --
> 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
>
>

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@hetnet.nl>
To: <mysql@lists.mysql.com>
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

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