SUM() In SQL Server

Question

Running SQL Server 2008 I can not wrap my mind around why these two queries, which are identical except for the first one has COALESCE() wrapped around the entire statement and the second one has COALESCE() wrapped around each field name in the query produce different results.

For example, shouldn't the COALESCE() statement encompass the entire line here so that any null value be filled with 0?

Create Table #sting
(
 item varchar(100)
 ,priceduringsale1 int
 ,priceduringsale2 int
 ,priceduringsale3 int
 ,priceduringsale4 int
)
Insert Into #sting Values
('sofa', '100', NULL, NULL, '200')
Select
item
,COALESCE(SUM(priceduringsale1+priceduringsale2+priceduringsale3+priceduringsale4),0)
FROM #sting
GROUP BY item

However, it does not, this produces a a return value of 0, I assume because anything + a NULL value is NULL, but I thought the COALESCE() statement would handle that.

Whereas this query produces the accurate result, but as you can see I individually SUM() and COALESCE() each field.

Select 
item
,COALESCE(SUM(priceduringsale1),0)
+COALESCE(SUM(priceduringsale2),0)
+COALESCE(SUM(priceduringsale3),0)
+COALESCE(SUM(priceduringsale4),0)
FROM #sting
GROUP BY item

What is it that causes these queries that are very similar to produce different returned result set?

• Dan Guzman: The first query will result is zero if any of the columns in the row are NULL whereas the second will evaluate each column for NULL independently.

Answer 1

You have to work from the inside out with function calls and parentheses. The first part evaluated will be...

priceduringsale1 + priceduringsale2 + priceduringsale3 + priceduringsale4

If any of those is NULL, that row's result will be NULL. So then you're summing up those terms, potentially with NULLs along for the ride, meaning if any of those columns happened to be null, that whole row would be left out of the sum (you may see a warning about an aggregate ignoring NULL values at this point):

SUM(priceduringsale1 + priceduringsale2 + priceduringsale3 + priceduringsale4)

Only then are you applying COALESCE, meaning that entire SUM() has to be NULL in order for it to be replaced with zero. COALESCE won't magically dig down into its arguments and replace individual NULL terms.

COALESCE(SUM(priceduringsale1 + priceduringsale2 + priceduringsale3 + priceduringsale4), 0)

If you COALESCE the individual columns first in order to replace them with zero, you will likely get the result you're expecting:

SUM(COALESCE(riceduringsale1, 0) + COALESCE(priceduringsale2, 0) + COALESCE(priceduringsale3, 0) + COALESCE(priceduringsale4, 0))

• Aaron: Since there is only one item being evaluated by coalesce would there be any difference in using the isnull function instead? Or is it just a matter of preference?
• db2: @Aaron In that scenario, I think it's just a matter of preference/ANSI-standardness. I typically use ISNULL out of habit/preference, then grumble about the difference when I'm doing something on MySQL.

Answer 2

first thing to understand is that:

select null + 7 -- this will return NULL

using COALESCE will help you, just as you can see on the code below:

drop table #sting
Create Table #sting
(
 item varchar(100)
 ,priceduringsale1 int
 ,priceduringsale2 int
 ,priceduringsale3 int
 ,priceduringsale4 int
)
Insert Into #sting Values
('sofa', '100', NULL, NULL, '200')
Select
item
,SUM(COALESCE(priceduringsale1,0) 
 +COALESCE(priceduringsale2,0) 
 +COALESCE(priceduringsale3,0) 
 +COALESCE(priceduringsale4,0) )
FROM #sting
GROUP BY item
go

there are cases when you want the calculation at hand

for those you can use computed columns

drop table #sting
Create Table #sting
(
 item varchar(100)
 ,priceduringsale1 int
 ,priceduringsale2 int
 ,priceduringsale3 int
 ,priceduringsale4 int
)
-- creating the calculated column my_sum
 alter table #sting
add my_sum AS COALESCE(priceduringsale1,0) 
 +COALESCE(priceduringsale2,0) 
 +COALESCE(priceduringsale3,0) 
 +COALESCE(priceduringsale4,0) 
persisted
Insert Into #sting Values
('sofa', '100', NULL, NULL, '200')
select * from #sting

query plan for the first example:

enter image description here

query plan with the calculated column: enter image description here