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?
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With