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" <[EMAIL PROTECTED]>
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/[EMAIL PROTECTED]
