Calculate balance with mysql

Question

I have a table which contains the following data:

ID      In       Out 
1      100.00    0.00   
2       10.00    0.00   
3        0.00   70.00    
4        5.00    0.00    
5        0.00   60.00   
6       20.00    0.00

Now I need a query which gives me the following result:

ID      In       Out    Balance
1      100.00    0.00   100.00
2       10.00    0.00   110.00
3        0.00   70.00    40.00
4        5.00    0.00    45.00
5        0.00   60.00   -15.00
6       20.00    0.00     5.00

Is it possible to do this with one query, without using a trigger or stored procedures?

Simon at The Access Group · Accepted Answer · 2012-02-22T12:57:12.790

17

Short answer, yes

Longer answer, you can use a variable to tally it up as it iterates down the rows, i.e.

SELECT 
    `table`.`ID`,
    `table`.`In`,
    `table`.`Out`,
    @Balance := @Balance + `table`.`In` - `table`.`Out` AS `Balance`
FROM `table`, (SELECT @Balance := 0) AS variableInit
ORDER BY `table`.`ID` ASC

The , (SELECT @Balance := 0) AS variableInit ensures that @Balance is initialised to 0 before you start. For each row it then sets @Balance to be @Balance + In - Out, and then outputs the calculated value.

Also it's worth making certain the ORDER is consistent as otherwise the Balance will vary depending on what order the rows are returned. If you wanted to then order it back to front, for example, you could use this as a subquery as then the outer query deals with the calculated values thus ensuring the Balance remains correct i.e.

SELECT
    `balanceCalculation`.`ID`,
    `balanceCalculation`.`In`,
    `balanceCalculation`.`Out`,
    `balanceCalculation`.`Balance`
FROM (
    SELECT 
        `table`.`ID`,
        `table`.`In`,
        `table`.`Out`,
        @Balance := @Balance + `table`.`In` - `table`.`Out` AS `Balance`
    FROM `table`, (SELECT @Balance := 0) AS variableInit
    ORDER BY `table`.`ID` ASC
) AS `balanceCalculation`
ORDER BY `balanceCalculation`.`ID` DESC

edited Feb 22 '12 at 12:57

answered Feb 22 '12 at 12:11

Simon at The Access Group

6,932
22
45

how with pagination, i think this query will not working well, the balance will not going well – Putra L Zendrato Nov 27 '16 at 12:25
@PutraLZendrato I'm afraid I don't understand the question – Simon at The Access Group Nov 29 '16 at 10:20
1

Hello Simon, I mean, how if the data row is big, Example, we have 100 data, but will not going load in one page. So, we separate into 2 page (pagination working). I think the running balance will not working. – Putra L Zendrato Nov 30 '16 at 13:00
You can limit the outer query as the subquery calculates derived values from the full dataset. You can imagine `balanceCalculation` there as being just another table. The limit does not change the totals, only the subset of data that you select from it. – Simon at The Access Group Feb 04 '17 at 13:02
Is there anyway to get this to work with pagination? – tinyCoder Mar 30 '18 at 01:17
@tinyCoder as long as you are ordering by ID, then you could do a subquery to `SELECT DISTINCT table.ID FROM table ORDER BY table.ID ASC LIMIT 0,10` for example, and then use that as an INNER JOIN within the `balanceCalculation` subquery. Alternatively for the less efficient method you can just LIMIT the outer query. But it would be more efficient to limit the IDs which are being summed up in the internal query. But I already mentioned that in the comment from 2017. – Simon at The Access Group Apr 04 '18 at 10:13
Issue solved, check this if you where interested, thank you. https://stackoverflow.com/questions/49836805/double-entry-accounting-pagination-issue/ – tinyCoder Apr 15 '18 at 11:03
It will, but if you want to order based on totals over the whole table then you have no choice but to select the entire table. You could add a WHERE clause to the subquery though if you are only interested in a specific set of rows, and then it will only sum up over that subset of data. – Simon at The Access Group Jul 30 '18 at 07:17

John Woo · Answer 2 · 2012-02-22T12:34:47.243

4

The most simple answer would be:

SELECT `ID`, 
       `In`, 
       `Out`, 
       @running_bal := @running_bal + (`In` - `Out`)  as `Balance`
FROM   tableName, (SELECT @running_bal := 0) tempName

edited Feb 22 '12 at 12:34

answered Feb 22 '12 at 12:16

John Woo

258,903
69
498
492

But those new buttons though.. · Answer 3 · 2017-01-27T05:32:03.790

0

A simple LEFT JOIN will suffice:

SELECT t.ID, t.In, t.Out, (SUM(t2.In) - SUM(t2.Out)) Balance
FROM mytable t
    LEFT JOIN mytable t2 ON b2.ID <= b.ID
GROUP BY b.ID

Or subquery (which as it turns out is about twice as fast)

SELECT t.ID, t.In, t.Out,
    (SELECT SUM(t2.In) - SUM(t2.Out) FROM mytable t2 WHERE t2.ID <= t.ID) Balance
FROM mytable t;

edited Jan 27 '17 at 05:32

answered Jan 27 '17 at 04:58

But those new buttons though..

21,377
10
81
108

Calculate balance with mysql

3 Answers3

Linked