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

196 Reputation points
2021-10-21T22:16:02.94+00:00

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 )

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,612 questions

1. 14,581 Reputation points
2021-10-22T02:06:06.457+00:00

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

1. 8,466 Reputation points
2021-10-22T03:47:02.973+00:00

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

2. 196 Reputation points
2021-10-22T14:50:30.073+00:00