SQL query to return null instead of SUM when null exists

Question

I have simple table, by example:

create table #test(x int, y int)
insert into #test values(null, 1)
insert into #test values(1, 1)
insert into #test values(1, 2)
insert into #test values(2, 2)
insert into #test values(3, 2)

I need to get the sum with grouping, but if the group has a null value, then get null instead of sum. I can do it with two query:

1) select y, case when AVG(x)<>SUM(x)/COUNT(*) then null else SUM(x) end from #test
group by y
2) select y, CASE WHEN EXISTS(select * from #test innerTest where innerTest.y=outerTest.y and x is null) then null else sum(x) end 
from #test outerTest group by y

Which query is better in performance? And are there other solutions to do it?

`AVG(x)<>SUM(x)/COUNT(*)` why does this work ? isn't null considered a 0 value ? how would avg be different from sum/count ? — njzk2, Oct 24 '13 at 18:42

score 7 · Accepted Answer · answered Oct 24 '13 at 19:15

7

you can check if group has nulls like this:

select
    y,
    case when count(x) <> count(*) then null else sum(x) end
from test
group by y

sql fiddle demo

I'm 99.99% sure that this one will run faster then one with subquery.

answered Oct 24 '13 at 19:15

Roman Pekar

107,110
28
195
197

Better than my answer. Thanks for posting it. – bopapa_1979 Oct 25 '13 at 21:15

bopapa_1979 · Answer 2 · 2013-10-24T19:13:31.813

The first option will run faster. The CASE WHEN EXISTS in the 2 example will require an additional table scan that is not required in the first example. You can see this in the query plan if you are using SQL server.

Here is the TSQL code to execute your above statements and show the execution plans in your results if you are using SQL server:

CREATE TABLE #test ( x INT, y INT )
INSERT  INTO #test VALUES  ( NULL, 1 )
INSERT  INTO #test VALUES  ( 1, 1 )
INSERT  INTO #test VALUES  ( 1, 2 )
INSERT  INTO #test VALUES  ( 2, 2 )
INSERT  INTO #test VALUES  ( 3, 2 )
GO

SET STATISTICS PROFILE  ON
GO

SELECT  y ,
        sumG = CASE WHEN AVG(x) <> SUM(x) / COUNT(*) THEN NULL
                    ELSE SUM(x)
               END
FROM    #test
GROUP BY y
ORDER BY y ,
        SUM(X) DESC

SELECT  y ,
        sumG = CASE WHEN EXISTS ( SELECT    *
                                  FROM      #test innerTest
                                  WHERE     innerTest.y = outerTest.y
                                            AND x IS NULL ) THEN NULL
                    ELSE SUM(x)
               END
FROM    #test outerTest
GROUP BY y
ORDER BY y ,
        SUM(X) DESC
GO

SET STATISTICS PROFILE  OFF
GO

DROP TABLE #test;
GO

If you are using another DB engine, I don't know how to show you the execution plan off the top of my head, but most of them will have a way to do so.

NOTE: I do not know of a faster way to do this, but that doesn't mean there isn't one.

For more information on displaying SQL Server execution plans, see this excellent post: http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan. — bopapa_1979, Oct 24 '13 at 19:14

SQL query to return null instead of SUM when null exists

2 Answers2