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;

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

"Maxim Demenko" <mdemenko@arcor.de> wrote in message
news:44f2d28f$0$20028$9b4e6d93@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