# Calculate row value based on previous and actual row values

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)

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.

• Are you able to join the debit and credit fields into one field? If so, then you can use negative values as debit and positive values as credit. Mar 6, 2015 at 2:31
• For future questions (since this has been answered), post code in text, not print-screens. Also include the `CREATE TABLE` statements and sample data (with `INSERT`). Mar 7, 2015 at 0:03
• In homage to your answer @ypercube, for anyone reading this I added a CREATE TABLE and INSERT example below dba.stackexchange.com/a/183207/131900 Aug 10, 2017 at 18:36

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)
``````
• MySQL 8.0 supports Window functions, and above solution using OVER, now works verbatim and produces the wanted result. Feb 2, 2021 at 11:17

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;
``````

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)
*/
``````

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;
``````

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 :)

• 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. Mar 6, 2015 at 14:40