Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Problem w/ null values when using SUM()

Author  Topic 

crugerenator
Posting Yak Master

126 Posts

Posted - 2009-12-30 : 15:24:29
I get this warning when using SUM()...

Null value is eliminated by an aggregate or other SET operation.


Select p.pagename,
Count(t.userid) as PageCount,
SUM(t.renderTime) as totalRender

from Pageindex p,
PageTrack t

Where t.pageid = p.pageid
AND Date LIKE '%Dec%'
AND Date Like '%2009%'

Group by p.pagename

Order by PageCount desc


I can't figure out why this is happening... I thought SQL counted nulls as 0's when summing.

Thanks!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-30 : 15:34:54
rather than change it to 0, it removes it. on integers, it's the same result...

see this simple example
create table #t (a int, b int);

insert #t
select 1, 1 union all
select 1, 1 union all
select 1, 1 union all
select 2, 1 union all
select 2, null union all
select 2, 1

select a, sum(b)
from #t
group by
a

drop table #t
Go to Top of Page

crugerenator
Posting Yak Master

126 Posts

Posted - 2009-12-30 : 15:38:24
got ya, used SUM(ISNULL(column,0)). worked like a charm... only now I'm getting this error:

Arithmetic overflow error converting expression to data type int.
Go to Top of Page

crugerenator
Posting Yak Master

126 Posts

Posted - 2009-12-30 : 15:43:41
quote:
Originally posted by crugerenator

got ya, used SUM(ISNULL(column,0)). worked like a charm... only now I'm getting this error:

Arithmetic overflow error converting expression to data type int.



This took care of it...

SUM(CAST(p.renderTime AS BigInt)) as totalRender

Thanks for the help
Go to Top of Page
   

- Advertisement -