Mysql – Calculate row value based on previous and actual row values

MySQLrow

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.

Best Answer

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)

Related Solutions

Mysql – how to set a row’s value from a certain row’s value

Assuming that the tstamp has a UNIQUE constraint:

UPDATE activities AS a
  JOIN
  ( SELECT cur.tstamp,
           SUM(prev.amount) AS balance 
    FROM activities AS cur
      JOIN activities AS prev
        ON prev.tstamp <= cur.tstamp
    GROUP BY cur.tstamp
  ) AS p
  ON p.tstamp = a.tstamp
SET a.balance = p.balance ;

Tested: SQL-Fiddle

MySQL has also a feature to use ORDER BY with an UPDATE, which you can combine with the use of variables:

SET @b := 0 ;
UPDATE activities
SET balance = (@b := amount + @b)
ORDER BY tstamp ;

Tested: SQL-Fiddle

Mysql – Get previous row and compare values

You have replaced the previous column reference in the CASE expression with the same subquery that you are using earlier to calculate the value of previous. While that certainly resolves the issue of referencing a calculated column at the same nesting level, duplication of code is far from an ideal solution.

You can avoid the duplication by abandoning the CASE expression completely as unnecessary. All it does is producing a "yes"/"no" result based on the values of the current and previous calledstationid only to be compared to "yes" at the outer level. Instead of checking on the value of achou, your outer level can be checking on the values of calledstationid and previous directly, like this:

SELECT COUNT(achou) as total
FROM (
    SELECT
        h.calledstationid,
        (
            SELECT calledstationid
            FROM radius.radacct_historical as sub 
            WHERE sub.callingstationid = '54:9F:13:27:31:7F'
              AND sub.acctstarttime < h.acctstarttime
            ORDER BY sub.acctstarttime DESC
            LIMIT 1 
        ) as previous,
    FROM radius.radacct_historical  AS h 
    WHERE h.callingstationid = '54:9F:13:27:31:7F'
) as S WHERE S.calledstationid = 34 AND S.previous = 42;

As you can see, I have also removed other elements from the query, like most of the columns returned by the nested SELECT (acctstarttime, acctstoptime, radacctid, callingstationid – they neither participate in the output nor affect the count result) and the nested ORDER BY h.acctstarttime ASC (there is no need to sort rows before counting them).

Best Answer

Related Solutions

Mysql – how to set a row’s value from a certain row’s value

Mysql – Get previous row and compare values

Related Question