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 )

- Home
- Anonymous
- Sign in to post
- Post
- Explore
- Tags
- Questions
- Site feedback
- Articles
- Users

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 )

Comment

0

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

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".

0

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

0

**11** people are following this question.

- About
- ·
- FAQ
- ·
- Privacy
- ·
- Copyright © 2010-22 DZone, Inc.