I’ve gotten a bunch of questions recently about the following warning:
Warning: Null value is eliminated by an aggregate or other SET operation.
Hopefully, with this post, I’ll be able to explain what this error means and give some examples about what (if anything) you should do about it.
The aggregate or set operation is likely a SUM or COUNT. Microsoft has a list here of all the aggregate functions. So essentially, when you see this error message, it means you’re aggregating a bunch of values and one of the values that is being summed or counted is null. Consider the following example:
CREATE TABLE tempTable
(
i float
)
GO
INSERT tempTable VALUES (1);
INSERT tempTable VALUES (2);
INSERT tempTable VALUES (NULL);
GO
SELECT COUNT(i) AS [count i:] FROM temptable
SELECT COUNT(1) AS [count 1:] FROM temptable
SELECT COUNT(*) AS [count *:] FROM temptable
SELECT SUM(i) AS [sum i:] FROM temptableAnd the results would look something like:
count i:
-----------
2
Warning: Null value is eliminated by an aggregate or other SET operation.
count 1:
-----------
3
count *:
-----------
3
sum i:
----------------------
3
Warning: Null value is eliminated by an aggregate or other SET operation.So now you have to decide what do you want to do about it:
Change the null to a value that is not null:
This is the most popular approach. For example:
select sum(i)
becomes
select sum(ISNULL(i, 0))
or the equivalent COALESCE if you like the ANSI standard:
select sum(COALESCE(i, 0))
And
select count(i)
should be written
select count(*)
or
select count(1)
any way.
Filter out the rows that can be null
Not so popular. Often it’s because of other requirements for the query. But if at all possible, try something like this:
SELECT SUM(i) FROM tempTable WHERE NOT i IS NULLSuppress the warning
The ostrich’s answer with:
SET ANSI WARNINGS OFF
Or finally:
Ignore the warning
A variation of the previous suggestion. If the results are what you’re looking for, then don’t worry about it.
