SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Problem w/ null values when using SUM()
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

crugerenator
Posting Yak Master

126 Posts

Posted - 12/30/2009 :  15:24:29  Show Profile  Send crugerenator an AOL message  Reply with Quote
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

USA
5071 Posts

Posted - 12/30/2009 :  15:34:54  Show Profile  Visit russell's Homepage  Reply with Quote
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 - 12/30/2009 :  15:38:24  Show Profile  Send crugerenator an AOL message  Reply with Quote
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.

Edited by - crugerenator on 12/30/2009 15:38:36
Go to Top of Page

crugerenator
Posting Yak Master

126 Posts

Posted - 12/30/2009 :  15:43:41  Show Profile  Send crugerenator an AOL message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000