We help IT Professionals succeed at work.

Sum Case in MS SQl

Ho can I make a sum case statement work

                    when a.var1 = 9 then 0 else a.var1  +
                        when a.var2 = 9 then 0 else a.var2          )
      ) as sumbeg,
Watch Question

Jason YousefSr. BI  Developer

I'm not sure what you're trying to do here !! the query doesn't to make sense

anyway test that, if it didn't 'work fr, please attache an example..
Select A + B from (

Select 'A' = case when a.var1 = 9 then 0 else a.var1 END , 
	'B' = Case when a.var2 = 9 then 0 else a.var2 END

from Table

Open in new window


I have a big  query in a store procedur. So I am taking only the part that I need to change.
This is how it was originally,
Select A, B, C, D,
      SUM((ISNULL(s.var1,0)) + (ISNULL(s.var2,0))  as sumbeg,
        SUM((ISNULL(a.var1,0)) + (ISNULL(a.var2,0))  as summid,

All I need to do is to exclude the value  s.var1 = 9 and make it 0 the same for var2

Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
SUM((ISNULL(NULLIF(s.var1, 9),0)) + (ISNULL(NULLIF(s.var2, 9),0))  as sumbeg,
SUM((ISNULL(NULLIF(a.var1, 9),0)) + (ISNULL(NULLIF(a.var2, 9),0))  as summid,


thanks a lot. U are awesome