How can I get sum values using a SQL case statement

Question

I am using the query shown here to sum of financial target first statement(expd) is working fine it's showing sum where socode is not equal to 29 and 42. But the second statement I want to get sum only where socode = 42 and 29 and 20 but its giving 0.00 value while 29 and 42 having its own value.

select 
   isnull(CONVERT(DECIMAL(10, 2),
           SUM(CASE WHEN SoCode <> 42 and SoCode <> 29 and SoCode <> 20 
                       THEN (Financialtarget)/100000 ELSE 0 END)), 0) as expd,
   isnull(CONVERT(DECIMAL(10, 2),
           SUM(CASE WHEN (SoCode = 29) and (SoCode = 42)  
                      THEN (Financialtarget)/100000 ELSE 0 END)), 0) as expd1
from 
    MPR 
where 
    month <= 7 
    and mpryear = '2014-15' 
    and Division = '12'

Thanks

Please format the code.

artm
– artm

2014-10-04 11:16:03 +00:00
Commented Oct 4, 2014 at 11:16 — artm
– artm, Commented Oct 4, 2014 at 11:16

ekad · Accepted Answer · 2014-10-04 11:24:26Z

expd1 is 0 because the condition (SoCode=29) and (SoCode=42) is always wrong. It should be using OR like (SoCode=29) OR (SoCode=42) OR (SOCode=20) instead of using AND

SELECT 
    ISNULL(CONVERT(DECIMAL(10,2),
    SUM(CASE WHEN SoCode <> 42 AND SoCode <> 29 AND SoCode <> 20 
        THEN (Financialtarget)/100000 
        ELSE 0 END)),0) AS expd, 
    ISNULL(CONVERT(DECIMAL(10,2),
        SUM(CASE WHEN (SoCode = 29) OR (SoCode = 42) OR (SOCode = 20) 
        THEN (Financialtarget)/100000 
        ELSE 0 END)),0) AS expd1
FROM MPR 
WHERE month <= 7 AND mpryear = '2014-15' AND Division = '12'

Alternatively you can also use IN

SELECT 
    ISNULL(CONVERT(DECIMAL(10,2),
    SUM(CASE WHEN SoCode <> 42 AND SoCode <> 29 AND SoCode <> 20 
        THEN (Financialtarget)/100000 
        ELSE 0 END)),0) AS expd, 
    ISNULL(CONVERT(DECIMAL(10,2),
        SUM(CASE WHEN SoCode IN (29,42,20)
        THEN (Financialtarget)/100000 
        ELSE 0 END)),0) AS expd1
FROM MPR 
WHERE month <= 7 AND mpryear = '2014-15' AND Division = '12'

Collectives™ on Stack Overflow

How can I get sum values using a SQL case statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related