Conditional Sum Syntax Are these two methods both correct or do they calculate or return results differently?

Question

Method_1=Case when WS_Cost_Code = 1 then SUM(WS_Extended) else 0 end
Method_2=SUM(case when WS_Cost_Code=1 then WS_Extended_Cost else 0 end )

Answer 1

Hi @JM12377 ,

The syntax of these two methods is correct.

Method_1:

    Case when WS_Cost_Code = 1 then SUM(WS_Extended) else 0 end  

In the first method, when WS_Cost_Code = 1, the sum of all WS_Extended corresponding to WS_Cost_Code = 1 is returned, and 0 is returned for all WS_Cost_Code <>1.
Method_2:

    SUM(case when WS_Cost_Code=1 then WS_Extended_Cost else 0 end )  

In the second method, when WS_Cost_Code = 1, the value of WS_Extended is returned, and when WS_Cost_Code <> 1, it returns 0. Then the returned values are summed. If you specify the GROUP BY WS_Cost_Code clause, the returned result is the same as in the first method. If not specified, only one value is returned.

Below is the data I tested：

select * from t  

select   
case when No1 = 3 then SUM(No2) else 0 end m1  
from t  
group by No1  
  
select  
sum(case when No1=3 then No2 else 0 end ) m2  
from t  
  
select case when No1 = 3 then SUM(No2) else 0 end m1  
,sum(case when No1=3 then No2 else 0 end ) m2  
from t  
group by No1  

Output：

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

To know whether they are the same or not, we would need to see a complete query. For example, if your table is

Declare @Test Table(Category int, WS_Cost_Code int, WS_Extended int);  
Insert @Test(Category, WS_Cost_Code, WS_Extended) Values  
(1, 1, 10),  
(1, 1, 20),  
(1, 0, 30),  
(2, 1, 40),  
(2, 0, 50),  
(2, 1, 60);  

Then

Select Case when WS_Cost_Code = 1 then SUM(WS_Extended) else 0 end From @Test Group By WS_Cost_Code  
Select SUM(case when WS_Cost_Code=1 then WS_Extended else 0 end ) From @Test Group By WS_Cost_Code  

will both work. But if you do

Select Category, Case when WS_Cost_Code = 1 then SUM(WS_Extended) else 0 end From @Test Group By Category  
Select Category, SUM(case when WS_Cost_Code=1 then WS_Extended else 0 end ) From @Test Group By Category  

The second one will work, but the first will fail with error 8120.

I would recommend always using method 2.

Tom

Answer 3

Thank you both very much. I appreciate both of your helpful answers.