Warning: Null value is eliminated by an aggregate

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

SQL Server 2000 Forums

SQL Server Development (2000)

Warning: Null value is eliminated by an aggregate

Author

Topic

Clages1
Yak Posting Veteran

69 Posts

Posted - 2009-08-11 : 09:39:20

Hi , i oftem get this message when i am using
select sum(x * y) from anytable

but i do a
select sum x , y from anytable
and i do not see any NULL values.

why do i get this message?

Tks

Clages

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-11 : 17:23:48

You are SUMming values and one or more of those values is NULL so sql ignores (eliminates) them.

You can just ignore the warning message or you can SET ANSI_WARNINGS OFF to not display the warning message.

You can get rid of that message by using COALESCE. But, that can also change your results if you are using other aggregate functions(average or count for example):

DECLARE @Foo TABLE (ID INT)

INSERT @Foo
SELECT 1
UNION ALL SELECT 3
UNION ALL SELECT 5
UNION ALL SELECT NULL
UNION ALL SELECT 7
UNION ALL SELECT 9
UNION ALL SELECT NULL
UNION ALL SELECT 11
UNION ALL SELECT 13

SELECT COUNT(ID), COUNT(COALESCE(ID, 0))
FROM @Foo

SELECT SUM(ID),SUM(COALESCE(ID, 0))
FROM @Foo

SELECT AVG(ID), AVG(COALESCE(ID, 0))
FROM @Foo

Clages1
Yak Posting Veteran

69 Posts

Posted - 2009-08-25 : 12:42:06

Tks for answer
But i assure you
there are no null values
when i select x, y from tables

this is the problem
if there are no null values why
when i use SUM i get this message

tks

Carlos Lages

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources