Re: sum with null value

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 Received on Mon Aug 28 2006 - 06:24:50 CDT