Change aggregate functions to output NULL when a element is NULL

Question

Every question I search for about the warning

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

Typically people want to treat the NULL values as 0. I want the opposite, how do I modify the following stored procedure to make it return NULL instead of 1?

CREATE PROCEDURE TestProcedure 
AS
BEGIN
select cast(null as int) as foo into #tmp

insert into #tmp values (1)

select sum(foo) from #tmp
END
GO

I thought it would be SET ANSI_NULLS ON (I tried before the declaration, within the procedure itself, and before executing the procedure in my test query) but that did not appear to change the behavior of SUM(.

so you want aggregate functions to return null if any element of the collection is null? — Mark W
– Mark W, Commented Mar 3, 2014 at 21:53

Gordon Linoff · Accepted Answer · 2014-03-03 21:52:51Z

6

The sum() function automatically ignores NULL. To do what you want, you need an explicit checK:

select (case when count(foo) = count(*) then sum(foo) end)
from #tmp;

If you want to be explicit, you could add else NULL to the case statement.

The logic behind this is that count(foo) counts the number of non-NULL values in foo. If this is equal to all the rows, then all the values are non-NULL. You could use the more verbose:

select (case when sum(case when foo is null then 1 else 0 end) > 0
             then sum(foo)
        end)

And, I want to point out that the title is quite misleading. 1 + NULL = NULL. The issue is with the aggregation functions, not the arithmetic operators.

answered Mar 3, 2014 at 21:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Scott Chamberlain Over a year ago

Would that cause a large performance penalty on large tables due to counting twice?

D Stanley Over a year ago

Would an EXISTS be faster?

Gordon Linoff Over a year ago

@ScottChamberlain . . . You probably won't notice the difference in performance. Most of the time of the query will be spent looping through the data pages and fetching them from disk. A count() should be minor overhead in comparison with that effort.

Gordon Linoff Over a year ago

@DStanley . . . I doubt it. The query has to do an aggregation anyway, so adding a join (which an exists implicitly does) doesn't seem like it will speed it up. Of course, the OP can try and I might be wrong, but that's my intuition.

Martin Smith Over a year ago

Depends. If foo is indexed it could determine that a NULL exists quickly and thus avoid the whole scan though.

|

D Stanley · Accepted Answer · 2014-03-03 21:57:01Z

2

Looking for a null value with EXISTS may be the fastest:

SELECT
    CASE WHEN EXISTS(SELECT NULL FROM tmp WHERE foo IS NULL)
        THEN NULL
        ELSE (SELECT sum(foo) from tmp)
    END

answered Mar 3, 2014 at 21:57

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Comments

Nicholas Carey · Accepted Answer · 2014-03-03 22:01:42Z

0

Just say

select case sign(sum(case when foo is null then 1 else 0 end))
       when 1 then null
       else        sum(foo)
       end
 from some_table
 ...
 group by
 ...

That's about all you need.

answered Mar 3, 2014 at 22:01

Nicholas Carey

75.1k16 gold badges96 silver badges138 bronze badges

Collectives™ on Stack Overflow

Change aggregate functions to output NULL when a element is NULL

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related