Calculate row value based on previous and actual row values

Question

Hi everyone and thanks for your help.
I have the following situation: a table called statements that contains fields id(int), stmnt_date(date), debit(double), credit(double) and balance(double) Structure of my table

I want to calculate the balance following these rules:

The first row balance (chronologically) = debit - credit and for the rest of the rows

current row balance = chronologically previous row balance + current row debit - current row credit

As you can see on the picture above the rows are not arranged by date and that's why I used the word chronologically twice to emphasize on the importance of the stmnt_date value.

Thank you very much for your help.

Answer 1

Assuming that stmnt_date has a UNIQUE constraint, this would be fairly easy with window/analytic functions:

SELECT 
 s.stmnt_date, s.debit, s.credit,
 SUM(s.debit - s.credit) OVER (ORDER BY s.stmnt_date
 ROWS BETWEEN UNBOUNDED PRECEDING
 AND CURRENT ROW)
 AS balance
FROM
 statements AS s
ORDER BY
 stmnt_date ;

Unfortunately, MySQL does not (yet) have implemented analytic functions. You can solve the problem either with strict SQL, by self-joining the table (which should be rather inefficient although working 100%) or by using a specific MySQL feature, variables (which would be quite efficient but you'd have to test it when upgrading mysql, to be sure that the results are still correct and not mangled by some optimization improvement):

SELECT 
 s.stmnt_date, s.debit, s.credit,
 @b := @b + s.debit - s.credit AS balance
FROM
 (SELECT @b := 0.0) AS dummy 
 CROSS JOIN
 statements AS s
ORDER BY
 stmnt_date ;

With your data, it will result in:

+------------+-------+--------+---------+
| stmnt_date | debit | credit | balance |
+------------+-------+--------+---------+
| 2014年05月15日 | 3000 | 0 | 3000 |
| 2014年06月17日 | 20000 | 0 | 23000 |
| 2014年07月16日 | 0 | 3000 | 20000 |
| 2014年08月14日 | 0 | 3000 | 17000 |
| 2015年02月01日 | 3000 | 0 | 20000 |
+------------+-------+--------+---------+
5 rows in set (0.00 sec)

• geertjanvdk: MySQL 8.0 supports Window functions, and above solution using OVER, now works verbatim and produces the wanted result.

Answer 2

I think you could try the following:

set @balance := 0;
SELECT stmnt_date, debit, credit, (@balance := @balance + (debit - credit)) as Balance
FROM statements
ORDER BY stmnt_date;

Answer 3

ypercube's answer is pretty spectacular (I had never seen a variable creation within a single query via a dummy select like that), so here is the CREATE TABLE statement for your convenience.

For tabular data images in Google Image Search, you can use https://convertio.co/ocr/ or https://ocr.space/ to convert it to a text document. Then if the OCR didn't detect columns properly, and you have a Mac, use TextWrangler with the option key held down to perform a rectangular selection and move the columns around. The combination of SQL editor like Sequel Pro, TextWrangler and a spreadsheet like Google Docs makes dealing with tab-separated tabular data extremely efficient.

If I could put all this in a comment I would, so please don't upvote this answer.

-- DROP TABLE statements;
CREATE TABLE IF NOT EXISTS statements (
 id integer NOT NULL AUTO_INCREMENT,
 stmnt_date date,
 debit integer not null default 0,
 credit integer not null default 0,
 PRIMARY KEY (id)
);
INSERT INTO statements
(stmnt_date , debit, credit) VALUES
('2014-06-17', 20000, 0 ),
('2014-08-14', 0 , 3000 ),
('2014-07-16', 0 , 3000 ),
('2015-02-01', 3000 , 0 ),
('2014-05-15', 3000 , 0 );
-- this is slightly modified from ypercube's (@b := 0 vs @b := 0.0)
SELECT 
 s.stmnt_date, s.debit, s.credit,
 @b := @b + s.debit - s.credit AS balance
FROM
 (SELECT @b := 0) AS dummy 
CROSS JOIN
 statements AS s
ORDER BY
 stmnt_date ASC;
/* result
+------------+-------+--------+---------+
| stmnt_date | debit | credit | balance |
+------------+-------+--------+---------+
| 2014年05月15日 | 3000 | 0 | 3000 |
| 2014年06月17日 | 20000 | 0 | 23000 |
| 2014年07月16日 | 0 | 3000 | 20000 |
| 2014年08月14日 | 0 | 3000 | 17000 |
| 2015年02月01日 | 3000 | 0 | 20000 |
+------------+-------+--------+---------+
5 rows in set (0.00 sec)
*/

Answer 4

Self joining tables it's not very fast on large tables. So dealing with this task on PostgreSQL I decided to use trigger function for calculate stored field "balance". All calculations happens only once for each row.

DROP TABLE IF EXISTS statements;
CREATE TABLE IF NOT EXISTS statements (
 id BIGSERIAL,
 stmnt_date TIMESTAMP,
 debit NUMERIC(18,2) not null default 0,
 credit NUMERIC(18,2) not null default 0,
 balance NUMERIC(18,2)
);
CREATE OR REPLACE FUNCTION public.tr_fn_statements_balance()
RETURNS trigger AS
$BODY$
BEGIN
 UPDATE statements SET
 balance=(SELECT SUM(a.debit)-SUM(a.credit) FROM statements a WHERE a.stmnt_date<=statements.stmnt_date)
 WHERE stmnt_date>=NEW.stmnt_date;
RETURN NULL;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;
CREATE TRIGGER tr_statements_after_update
 AFTER INSERT OR UPDATE OF debit, credit
 ON public.statements
 FOR EACH ROW
 EXECUTE PROCEDURE public.tr_fn_statements_balance();
INSERT INTO statements
(stmnt_date , debit, credit) VALUES
('2014-06-17', 20000, 0 ),
('2014-08-14', 0 , 3000 ),
('2014-07-16', 0 , 3000 ),
('2015-02-01', 3000 , 0 ),
('2014-05-15', 3000 , 0 );
select * from statements order by stmnt_date;

Answer 5

In, for example, MSSQL:

Use a with() statement to generate a CTE. This is essentially a temporary result set which will show the value of each row. You can use math in the with statement to create a column at the end, using math to show the total of the row is DEBIT-CREDIT. In your with statement you'll need to assign row numbers to each row, use the OVER clause of WITH () to order by stmnt_date.

Then, recursively join the table onto itself, using a.ROWNUMBER= b.ROWNUMBER-1 or +1 which will allow you to refer the a.total+b.total= total of this row and the previous row.

I appreciate I'm not providing the code however this is the practical method to achieve this. I can supply code if requested :)

• ypercubeᵀᴹ: The question is about MySQL. While it's not bad (on the contrary) to provide code of how this could be done with CTEs or window functions in DBMS that have it (like Postgres, SQL-Server, DB2, Oracle, ... the list is long), you should at least provide code about how to do this in MySQL.