Re: sum with null value

"Maxim Demenko" <mdemenko_at_arcor.de> wrote in message news:44f2d28f$0$20028$9b4e6d93_at_newsspool4.arcor-online.net...
>T schrieb:
>> Hi! I've got problem with sql statement..
>>
>> in dataset like
>> col1 col2
>> a 1
>> b 3
>> c 5
>> d null
>> e 2
>>
>> i must to sum col2, but if 1 single value of this column is null then sum
>> must result to null, according this i wrote statement:
>>
>> select sum(col2) from table1
>>
>> but this query results with 11.
>>
>> any idea??!!
>>
>> create table table1 (col1 char(1), col2 number);
>>
>>
>>
>> insert into table1 values('a',1);
>>
>> insert into table1 values('b',3);
>>
>> insert into table1 values('c',5);
>>
>> insert into table1 values('d',null);
>>
>> insert into table1 values('e',2);
>>
>>
>>
>> commit;
>>
>>
>>
>> select sum(col2) from table1;
>>
>>
>
> select decode(count(*),count(col2),sum(col2)) from table1;
>
> will give you null if any of values for col2 is null and the sum
> otherwise.
>
> Best regards
>
> Maxim

tnx Maxim it's working... i lost count(* ) from my mind :)))

also works...

 select (case when count(*) = count(col2) then sum(col2) else null end) from table1

best regards,
 Tomo Received on Mon Aug 28 2006 - 06:39:06 CDT