error: Null value is eliminated by an aggregate

[michelleqw]

Dear sql-server users,

We got an error:

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

Can someone explain what kind of error this is?

Nice regards,

Michelle.

 

[JamesLean]

This is not an error as such - it is just an informational message. All it means is that when you have used some aggregate function (SUM, COUNT etc) the calculation has ignored any NULL values that may have been in the column.

--James

 

[jby1]

You could eliminate this warning message by excluding NULLs in the query, eg

select sum(col1)
from tbl1
where col1 is not null

 

[Madawc]

Use Count(*) if you want to count everything; the number of rows in a table, regardless of what they contain.

Madawc Williams (East Anglia, UK)

 

[dbomrrsm]

However if for example you are doing an average and you would like the nulls to be counted as 0's (therefore not affecting the result you can do:

select avg(isnull(yourfield,0))
from yourtable

what this will do is replace any yourfield values that are null with a 0 this then means that the row will still be included in calculating the average.

[blue]DBomrrsm[/blue]

 

[michelleqw]

Thanks all of you for the information.

Michelle.