MySQL Debit/Credit cumulative balance

I would like a cumulative DR or CR balance for the following table:

+-----------+--------------+---------------+---------+
|        id |       Date   |       DR      |    CR   |
+-----------+--------------+---------------+---------+
|         1 |   2020-01-01 |           100 |       0 |
|         2 |   2020-01-01 |           200 |       0 |
|         3 |   2020-01-02 |             0 |     200 |
|         4 |   2020-01-02 |             0 |      50 |
|         5 |   2020-01-03 |            20 |       0 |
+-----------+--------------+---------------+---------+

For the above table, a cumulative Balance column would be:

+-----------+--------------+---------------+---------+----------+
|        id |       Date   |       DR      |    CR   |  Balance |
+-----------+--------------+---------------+---------+----------+
|         1 |   2020-01-01 |           100 |       0 |   100 DR |
|         2 |   2020-01-01 |           200 |       0 |   300 DR |
|         3 |   2020-01-02 |             0 |     300 |     0 DR |
|         4 |   2020-01-02 |             0 |      50 |    50 CR |
|         5 |   2020-01-03 |            20 |      0  |    30 CR |
+-----------+--------------+---------------+---------+----------+

Decreasing a DR below 0 makes it a CR

Decreasing a CR below 0 makes it a DR

Where balance is 0, value is 0 DR It could be calculated as CR’s being a negative value, but I would like the ‘DR’ and ‘CR’ text to show in the balance column after an abs numerical value.

mysql Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using EditLine wrapper

Thanks for your help

Answer

If you are running MySQL 8.0, you can use window functions:

select
    t.*,
    concat(
        abs(sum(dr - cr) over(order by date, id)),
        ' ',
        case when sum(dr - cr) over(order by date, id) >= 0 then 'DR' else 'CR' end
    ) balance
from mytable t

---

In earlier versions, one efficient option uses a session variable:

set @sm = 0;    
select id, date, dr, cr, concat(abs(bal), ' ', case when bal >= 0 then 'DR' else 'CR' end) balance
from (
    select 
        t.*,
        @sm := @sm + dr - cr bal
    from (select t.* from mytable t order by date, id) t
) t

Demo on DB Fiddle – both queries yield:

| id  | date       | dr  | cr  | balance |
| --- | ---------- | --- | --- | ------- |
| 1   | 2020-01-01 | 100 | 0   | 100 DR  |
| 2   | 2020-01-01 | 200 | 0   | 300 DR  |
| 3   | 2020-01-02 | 0   | 300 | 0 DR    |
| 4   | 2020-01-02 | 0   | 50  | 50 CR   |
| 5   | 2020-01-03 | 20  | 0   | 30 CR   |