# How to Calculate the count and Sum in

21 Reputation points
2021-02-17T07:55:27.38+00:00

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.

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

1. 24,171 Reputation points
2021-02-17T08:36:59.627+00:00

Hi @FijazM33449 ,

`````` 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

1. 24,171 Reputation points
2021-02-17T08:10:09.333+00:00

Hi @FijazM33449 ,

Welcome to Microsoft Q&A!

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

2. 24,171 Reputation points
2021-02-17T09:29:00.37+00:00

Hi @FijazM33449 ,

``````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

3. 1 Reputation point
2021-02-17T10:45:23.45+00:00

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

4. 21 Reputation points
2021-02-18T10:09:30.917+00:00

Thanks for the support.

I also did it using the LEAD() function.