ISNULL(SUM(MyCol), 0) vs. SUM(ISNULL(MyCol, 0)) vs. SUM(MyCol)

[LFCfan]

Hello

SQL Server 2000
I'm confused about the difference of the 3 expressions in my thread title - ie, why there isn't a difference when the following is executed:

--DDL
create table #tmp (b int)
insert into #tmp values (30)
insert into #tmp values (null)
insert into #tmp values (30)
insert into #tmp values (30)
insert into #tmp values (30)
--QUERY
select isnull(sum(b),0) SUM1, sum(isnull(b,0)) SUM2, sum(b) SUM3 from #tmp

--Result set
SUM1 SUM2 SUM3
120.00 120.00 120.00

The result set is the same for all three, whether ANSI_NULLS are set to OFF or ON

I was expecting the result set to look more like
SUM1 SUM2 SUM3
0.00 120.00 NULL

because of the NULL value in the column...?
Is this due to some DB setting other than ANSI_NULLS?

Hope this makes sense
Any pointers would be appreciated

Thanks
LFCfan

 

[SQLBill]

The ISNULL doesn't RETURN the value entered, 0 in this case: isnull(sum(b),0). ISNULL just says: if the value is NULL then use 0.

So,
create table #tmp (b int)
insert into #tmp values (30)
insert into #tmp values (null)
insert into #tmp values (30)
insert into #tmp values (30)
insert into #tmp values (30)

has these rows:
30
NULL
30
30
30

This, isnull(sum(b),0), effectively changes the above to be:

30
0
30
30
30

The SUM of which is 120

-SQLBill

 

[LFCfan]

sorry, i wasnt clear i guess
i thought the sum of null and non null values would return null, under certain DB settings

in which case -
isnull(sum(b),0) would return isnull(null, 0) = 0,
sum(isnull(b,0)) would return isnull(30,0) + isnull(null,0) + ... = 120,
and sum(b) would be null...?

so isnull(sum(b),0) = sum(isnull(b,0)) always?
it just seems like there should be a distinction, depending on what function is on the "outside"

thanks

 

[swampBoogie]

Null are always discarded when using aggregate function (except count(*)). The result of setting ANSI_NULLS to on is that any removed null values will generate a warning. The result of a sum function will be null if there is no rows that fulfills the search criteria.

SQLBills description is not correct.

His description holds for sum(isnull(b,0)) not isnull(sum(b),0).

 

[LFCfan]

thanks swampBoogie
i know why i've been confused now - i was remembering

select @a + @b

which would return null if either @a or @b (or both) were null

...so that was a waste of everyone's time! Sorry... )
thanks to both of you for taking the time

LFCfan