15,943,477 members
See more: , +
I want the output like below, i am having this four column, please help me to generate fifth column with sql query.

```31-Mar-13	Product-A	50000	0	50000
2-Apr-13	Product-A	0	2000	48000
4-Apr-13	Product-A	0	3000	45000
6-Apr-13	Product-A	0	2500	42500
9-Apr-13	Product-A	0	2500	40000
11-Apr-13	Product-A	0	3000	37000
15-Apr-13	Product-A	0	3000	34000
16-Apr-13	Product-A	0	2000	32000
18-Apr-13	Product-A	0	1000	31000
20-Apr-13	Product-A	0	2000	29000```
Posted

## Solution 1

Calculating simple running totals in SQL Server[^]

i am having two columns that is of one column i need balance qty
thatraja 16-Dec-13 5:18am
I said customize it. You need to subtract the 3rd column value from the final result, that's it. Try it yourself, that's easy
Maciej Los 16-Dec-13 6:15am
A10!

## Solution 3

Have a look at example:
SQL
```DECLARE @tmp TABLE(ID INT IDENTITY(1,1), PaymentIn INT, PaymentOut INT)

INSERT INTO @tmp (PaymentIn, PaymentOut)
VALUES(50000, 0), (0, 2000),
(0, 3000), (0, 2500),
(0, 2500), (0, 3000),
(0, 3000), (0, 2000),
(0, 1000), (0, 2000)

SELECT t1.ID, t1.PaymentIn, t1.PaymentOut, (SELECT SUM(t2.Balance)
FROM
(
SELECT ID, PaymentIn, PaymentOut, PaymentIn AS Balance
FROM @tmp
UNION ALL
SELECT ID, PaymentIn, PaymentOut, PaymentOut *-1 AS Balance
FROM @tmp
) AS t2 WHERE t2.ID<=t1.ID) AS Total
FROM @tmp AS t1```

Result:
```ID      Pa.In   Pa.Out  Total
1	50000	0	50000
2	0	2000	48000
3	0	3000	45000
4	0	2500	42500
5	0	2500	40000
6	0	3000	37000
7	0	3000	34000
8	0	2000	32000
9	0	1000	31000
10	0	2000	29000```

Amir Mahfoozi 16-Dec-13 7:28am
+5
Maciej Los 16-Dec-13 7:59am
Thank you, Amir ;)
thatraja 16-Dec-13 9:19am
You have plenty of time, 5!
Maciej Los 16-Dec-13 9:21am
Thank you ;)

## Solution 2

Hello Try this query.

`select s.col1 as Date,s.col2 as Product,s.col3 as Cr,s.col4 as Db,(select sum(col3)-sum(col4) from this_table t where t.Col1 < s.col1) from this_table s`