Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
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 #tselect 1, 1 union allselect 1, 1 union allselect 1, 1 union allselect 2, 1 union allselect 2, null union allselect 2, 1select a, sum(b)from #tgroup by adrop table #t
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.
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