# question

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

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 )

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JMs-0144,

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

image.png (13.8 KiB)
image.png (8.0 KiB)

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.