How to Calculate the count and Sum in

Question

Hello Team,

I have a table like below and its data .

Kindly help to write a query to get the result set like below.

THank you.

Answer 1

Hi @FijazM33449 ,

Please refer below update one:

 select BranchID,TerminalID,count(type) count, sum(amount) Amount  
from (  
select BranchID,TerminalID,type,amount   
from Calculate  
except  
select a.BranchID,a.TerminalID,a.type,a.amount  
from Calculate a  
inner join Calculate b on a.Amount=b.Amount  and a.BranchID=b.BranchID  
where  a.Type='Deposit' and b.Type='DepositReversal'   
except  
select b.BranchID,b.TerminalID,b.type,b.amount  
from Calculate a  
inner join Calculate b on a.Amount=b.Amount  and a.BranchID=b.BranchID  
where  a.Type='Deposit' and b.Type='DepositReversal' ) c  
group by BranchID,TerminalID  

Output:

BranchID TerminalID count Amount  
Branch01 T01 1 1000  
Branch02 T02 2 2500  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @FijazM33449 ,

Welcome to Microsoft Q&A!

Could you please doublecheck the Count and Sum of 'Branch01' and 'T01'? It could be 2000 instead of 1000. If 1000 is the correct one, please provide more details about this.

You could refer below query and check whether it is working:

create table Calculate   
(  
Date date,  
TransID int,  
BranchID varchar(20),  
TerminalID varchar(20),  
Type varchar(20),  
Amount int  
)  
  
insert into Calculate values  
('2021-02-16',7001,'Branch01','T01','Deposit',1000),  
('2021-02-16',7002,'Branch01','T01','Deposit',500),  
('2021-02-16',7003,'Branch02','T02','Deposit',2000),  
('2021-02-16',7004,'Branch02','T02','Deposit',500),  
('2021-02-17',7005,'Branch01','T01','DepositReversal',500)  
  
select BranchID,TerminalID,count(distinct type) Count, sum(amount) Sum  
from Calculate  
group by BranchID,TerminalID  

Output:

BranchID TerminalID Count Sum  
Branch01 T01 2 2000  
Branch02 T02 1 2500  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @FijazM33449 ,

Please also refer below method:

select BranchID,TerminalID  
,sum(iif(type='Deposit',1,-1)) count  
,sum(iif(type='Deposit',amount,amount*-1)) Amount  
from Calculate   
group by BranchID,TerminalID  

OR

select BranchID,TerminalID  
,sum(case when type='Deposit' then 1 when type='DepositReversal' then -1 end) count  
,sum(case when type='Deposit' then Amount when type='DepositReversal' then -1*Amount end) Amount  
from Calculate   
group by BranchID,TerminalID  

Output:

BranchID TerminalID count Amount  
Branch01 T01 1 1000  
Branch02 T02 2 2500  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

create table Calculate
(
Date date,
TransID int,
BranchID varchar(20),
TerminalID varchar(20),
Type varchar(20),
Amount int
)

insert into Calculate values
('2021-02-16',7001,'Branch01','T01','Deposit',1000),
('2021-02-16',7002,'Branch01','T01','Deposit',500),
('2021-02-16',7003,'Branch02','T02','Deposit',2000),
('2021-02-16',7004,'Branch02','T02','Deposit',500),
('2021-02-17',7005,'Branch01','T01','DepositReversal',500)

;with cte as
(
select Type, BranchID,TerminalID,case when type = 'Deposit' then sum(amount) else -sum(amount) end as Sum1,
case when type = 'Deposit' then count(type) else -count(type) end as Count
from Calculate
group by BranchID,TerminalID, Type
)

select BranchID,TerminalID,Sum(Count) as Count, Sum(Sum1) as Sum from cte group by BranchID,TerminalID

Answer 5

Thanks for the support.

I also did it using the LEAD() function.