question

JMs-0144 avatar image
0 Votes"
JMs-0144 asked JMs-0144 answered

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 )

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

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

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

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

142693-image.png

 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:
142713-image.png


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



image.png (13.8 KiB)
image.png (8.0 KiB)
5 |1600 characters needed characters left characters exceeded

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

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

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

5 |1600 characters needed characters left characters exceeded

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

JMs-0144 avatar image
0 Votes"
JMs-0144 answered

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

5 |1600 characters needed characters left characters exceeded

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