Re: SUM and NULL values
On 2 Jul 1997, Akis Xenophontos wrote:
> E-mail Address: akisx@logica.com
> Name: Akis Xenophontos
> Company: Logica UK Ltd
> Postal Address: London NW1
> Tel No: 0044171 446 1324
>
> Date: 16/06/97
>
> I have the following problem:
>
> select sum(a) + sum(b) + sum(c)
> from table_a
>
> returns null if columns values b are all null for example.
>
> Is there a way, within one SQL statement, to treat the NULL as a
> 0 and return the aggregate sum of columns a,b and c?
>
Akis,
Not really.
Well, hmm, but you can do it by getting your results into
a temp table and then aggregating the data. In SQL, at least
in Informix's SQL, you can't even get a row if any one of
these columns in the row is null. Here's a way to do it.
select sum (a) a,0 b,0 c
from table_a
into temp xxx with no log;
insert into xxx
select 0, sum (b), 0
from table_a;
insert into xxx
select 0, 0, sum(c)
from table_a;
select sum (a) + sum (b) + sum (c) total
from xxx;
Yours,
Nick
*********************************
Nick Nobbe, Library of Congress
NLS/BPH
mail: nnob@loc.gov
*********************************