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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
