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
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?