CODEWITHSUNDEEP
sql-serversql-server-2008
Royi Namir
Royi Namir

Reputation: 148524

sum in case vs case in sum in SQL server ?

Is there any difference between writing : 

select
...,
mySum= CASE 
           WHEN i  IS NULL THEN 0 ELSE  SUM(i) 
       END  
...

vs 

select
...,
mySum=  SUM( CASE WHEN i IS NULL THEN 0  ELSE i
             END
           ) 
...

Or is it just 100% the same ( logically and traps...)?

Upvotes: 1

Views: 1462

Answers (2)

Kaf
Kaf

Reputation: 33809

I think logically there is no difference. But I think you don't need to check for null in this case as aggregate functions ignore nulls

BTW, You can simplify case with a isnull as follows. (Edit: mistakenly added sum(isnull(i,0)) changed to Isnull(sum(i),0))

select Col1, Isnull(sum(i),0) as mySum
from yourTable
group by Col1

Upvotes: 1

Bridge
Bridge

Reputation: 30651

On my test data, I get the same results returned, and near identical execution plans:

enter image description here

All the same costs, just two operations reversed.

Upvotes: 3

Related Questions