Calculate row value based on previous and actual row values

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)

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

Tags:

Mysql

Row