We help IT Professionals succeed at work.

Sum Case in MS SQl

Ho can I make a sum case statement work

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

Jason YousefSr. BI  Developer

Commented:
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
)X

Open in new window

Author

Commented:
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
Commented:
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,

Author

Commented:
thanks a lot. U are awesome