adding columns with null values

[FrankW]

I am having a problem trying to sum data where some columns have null values in them. I return null when I should be returning a value due to the null in certain cells.

col001 col002 col003
150 100 null (col001+col002+col003) = NULL

Can anyone tell me how I can sum these colums while at the same time omitting or bypassing the null value. I am not able to remove the nulls from the table.

Thanks,

Frank

 

[ADB1]

You could try an If Then Else calculation on the column:

If (col003 is Null) then (0) else (col003)

This will convert the null values to numbers and enable you to sum, I think!

Cheers,

Adam.

 

[grega]

Some DB servers have an isnull() function to achieve this. In Sybase, you would say;

select sum(isnull(col1,0)) from table

Greg.

 

[Guest_imported]

hi ,

you can use NVL(value,substitute) - i.e. if value is NULL then the substitute one takes place - like this u can also find correct group functions results like avg. by keeping some default value for NULL .

you get NULL because any operation applied on NULL results in NULL. Except count().

pravin.

 

[bperry]

frankw:

I don't think there's a function called NVL in SQL Server (at least I have never heard of one.)

So grega's suggestion of ISNULL is a good solution. Just to elaborate on his suggestion, you would probably do something like:

select sum(isnull(col1,0)+
isnull(col2,0)+
isnull(col3,0)
)
from table

 

[MalcolmW]

The SUM function in SQL Server ignores null values, so just
select SUM(col1 + col2 + col3)
from table
will do the trick.
In general, aggregate functions ignore null values. COUNT is an exception, as it does count null values. Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[tlbroadbent]

The issue is not the aggregate Function Sum ignoring or not ignoring Nulls. The issue is that addition of three columns where one or more is Null yields Null.

The following query will yield Null in each row where one or more columns in the expression is Null.

Select col1+col2+col3 As Tot
From table

If you want to sum the expression, sum would ignore the expression anytime the result was Null. This would yield very incorrect results.

The following solves the problem.

Select
Isnull(col1,0)+Isnull(col2,0)+Isnull(col3,0) As Tot
From table

Various databases have different functions that are equivalent to IsNull as has been mentioned. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.