15,920,632 members
See more:
I want Balance Like this in SQL Query How to do this..??
```+------+-------+--------+---------+
|   Id | debit | credit | balance |
+------+-------+--------+---------+
|    1 |    10 |      0 |      10 |
|    2 |     0 |     40 |     -30 |
|    3 |    50 |      0 |      20 |
|    4 |     0 |     10 |      10 |
|    5 |     0 |     10 |       0 |
+------+-------+--------+---------+ ```

What I have tried:

```SELECT x.Id
, x.debit
, x.credit
, SUM(y.Balance) balance
FROM
(
SELECT *,debit-credit bal FROM Ledger
) x
JOIN
(
SELECT *,debit-credit bal FROM Ledger
) y
ON y.Id<= x.Id
GROUP
BY x.Id,x.debit,x.credit;```
Posted
Updated 27-Mar-22 19:56pm
PIEBALDconsult 19-Feb-18 9:04am
Look into Recursive Common Table Expressions.

## Solution 1

Agree with PIEBALDConsult, but I'll add this:

The balance for each successive row depends on the ending balance from the previous row. You can either write SQL to loop through each transactions one at a time or, as has been suggested, look into recursive expressions.

Try one or the other and then post what you've tried.

## Solution 2

I'd strongly recommend to read this: Calculating simple running totals in SQL Server[^].

## Solution 3

You can use the same concept from here: How do I account for null values in a running total?[^]

Only different is, first the query need to find the different between the debit and credit, then run the running total calculation. See below as an example.
SQL
```DECLARE @RunTotalTestData TABLE  (
Id    int not null identity(1,1) primary key,
Debit int null,
Credit int null
);

INSERT INTO @RunTotalTestData (debit, Credit) VALUES (10, 0);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (0, 40);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (50, 0);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (0, 10);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (0, 10);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (100, 10);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (0, 10);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (0, 110);
;WITH tempDebitCredit AS (
SELECT a.id, a.debit, a.credit, a.Debit - a.Credit 'diff'
FROM @RunTotalTestData a
)
SELECT a.id, a.Debit, a.Credit, SUM(b.diff) 'Balance'
FROM   tempDebitCredit a,
tempDebitCredit b
WHERE b.id <= a.id
GROUP BY a.id,a.Debit, a.Credit```

Output:
```id	Debit	Credit	Balance
1	10	     0	     10
2	0	     40	    -30
3	50	     0	     20
4	0	     10	     10
5	0	     10	      0
6	100	     10	     90
7	0	     10	     80
8	0	     110	-30```

Thank you very Much
kirthiga S 20-Sep-18 8:18am
Nice one

## Solution 4

select
x.id,
(sum(y.bal)-x.vin)+x.vout as Opening_Balance,
x.vin,x.vout,sum(y.bal) Closing_Balance
from
(select *,vin-vout bal from tblbalancesheet) x
join
(select *,vin-vout bal from tblbalancesheet) y
ON y.id<=x.id
group by x.id,x.vin,x.vout,x.vdate

## Solution 5

I have added the ability to get the Brought Forward balance to Brian Solution above

SQL
```WITH tempDebitCredit AS (
Select 0 As Details_ID, null As Creation_Date, null As Reference_ID, 'Brought Forward' As Transaction_Kind, null As Amount_Debit, null As Amount_Credit, isNull(Sum(Amount_Debit - Amount_Credit), 0) 'diff'
From _YourTable_Name
where Account_ID = @Account_ID
And Creation_Date < @Query_Start_Date
Union All
SELECT a.Details_ID, a.Creation_Date, a.Reference_ID, a.Transaction_Kind, a.Amount_Debit, a.Amount_Credit, a.Amount_Debit - a.Amount_Credit 'diff'
FROM _YourTable_Name a
where Account_ID = @Account_ID
And Creation_Date >= @Query_Start_Date And Creation_Date <= @Query_End_Date
)

SELECT a.Details_ID, a.Creation_Date, a.Reference_ID, a.Transaction_Kind,
a.Amount_Debit, a.Amount_Credit, SUM(b.diff) 'Balance'
FROM   tempDebitCredit a, tempDebitCredit b
WHERE b.Details_ID <= a.Details_ID
GROUP BY a.Details_ID, a.Creation_Date, a.Reference_ID, a.Transaction_Kind,
a.Amount_Debit, a.Amount_Credit
Order By a.Details_ID Desc```

Tested on Microsoft SQL Server

## Solution 6

I think the simple way is follow, this script run on SQL Server.

Test data:
```DECLARE @TestData TABLE  (
Id    int not null identity(1,1) primary key,
Debit int null,
Credit int null
);

INSERT INTO @TestData (debit, Credit) VALUES (10, 0);
INSERT INTO @TestData (debit, Credit) VALUES (0, 40);
INSERT INTO @TestData (debit, Credit) VALUES (50, 0);
INSERT INTO @TestData (debit, Credit) VALUES (0, 10);
INSERT INTO @TestData (debit, Credit) VALUES (0, 10);
INSERT INTO @TestData (debit, Credit) VALUES (100, 10);
INSERT INTO @TestData (debit, Credit) VALUES (0, 10);
INSERT INTO @TestData (debit, Credit) VALUES (0, 110);```

The main query:

```select * from @TestData
DECLARE @i integer=1;
DECLARE @b integer=0;

declare @temp Table (
Id    integer not null,
Debit integer not null,
Credit integer not null,
Balance integer
)
while @i <= (select count(*) from @TestData)
begin
insert into @temp select * , (@b + debit) - credit as b from @TestData a where id = @i
set @b = (select balance from @temp where Id = @i);
set @i = @i+1
end
select * from @temp```

And you can see the result as follow !
Input data:
```Id	Debit	Credit
1	10	0
2	0	40
3	50	0
4	0	10
5	0	10
6	100	10
7	0	10
8	0	110```

Output:
```Id	Debit	Credit	Balance
1	10	0	10
2	0	40	-30
3	50	0	20
4	0	10	10
5	0	10	0
6	100	10	90
7	0	10	80
8	0	110	-30```

v2