We help IT Professionals succeed at work.

# Sum Case in MS SQl

on
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

## View Solution Only

Sr. 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
``````

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,

Commented:
thanks a lot. U are awesome