Running sums, chequebooks

from the Artful Common Queries page


A user variable can maintain a per-row cumulative sum of column values. Initialise it, then adjust its value as desired in the appropriate SELECT expression:

SET @total=0;
SELECT id, value, @total:=@total+value AS RunningSum
FROM tbl;

Chequebook balancing programs often use this pattern. This one tracks the running balance and how much money from the most recent deposit remains:

DROP TABLE IF EXISTS chequebook;
CREATE TABLE chequebook (
  entry_date timestamp default now() PRIMARY KEY,
  entry_item varchar(48) NOT NULL DEFAULT '',
  entry_amount decimal(10,2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO chequebook (entry_date,entry_item,entry_amount) VALUES 
('2010-01-02 12:34:00','Deposit A',215.56),
('2010-01-02 21:44:00','Withdrawal A' ,-23.34),
('2010-01-03 10:44:00','Withdrawal B',-150.15),
('2010-01-03 15:44:00','Deposit B',154.67),
('2010-01-04 18:44:00','Withdrawal C',-65.09),
('2010-01-05 08:44:00','Withdrawal D',-74.23),
('2010-01-06 14:44:00','Deposit C',325.12),
('2010-01-06 20:44:00','Withdrawal E',-80.12),
('2010-01-07 04:44:00','Withdrawal F',-110.34),
('2010-01-07 16:44:00','Withdrawal G',-150.25),
('2010-01-08 16:44:00','Withdrawal H',-23.90),
('2010-01-08 21:44:00','Withdrawal I',-75.66),
('2010-01-08 22:44:00','Deposit C',275.78),
('2010-01-09 11:44:00','Withdrawal K',-85.99),
('2010-01-09 21:44:00','Withdrawal J',-100.00);
 
set @depos=0;
set @total=0;
select 
  entry_date, 
  entry_item, 
  entry_amount, 
  if( entry_amount>0, @depos:=entry_amount, @depos:=@depos+entry_amount ) as depos_bal,
  @total:=@total+entry_amount as net_bal
from chequebook
order by entry_date;
+---------------------+--------------+--------------+-----------+---------+
| entry_date          | entry_item   | entry_amount | depos_bal | net_bal |
+---------------------+--------------+--------------+-----------+---------+
| 2010-01-02 12:34:00 | Deposit A    |       215.56 |    215.56 |  215.56 |
| 2010-01-02 21:44:00 | Withdrawal A |       -23.34 |    192.22 |  192.22 |
| 2010-01-03 10:44:00 | Withdrawal B |      -150.15 |     42.07 |   42.07 |
| 2010-01-03 15:44:00 | Deposit B    |       154.67 |    154.67 |  196.74 |
| 2010-01-04 18:44:00 | Withdrawal C |       -65.09 |     89.58 |  131.65 |
| 2010-01-05 08:44:00 | Withdrawal D |       -74.23 |     15.35 |   57.42 |
| 2010-01-06 14:44:00 | Deposit C    |       325.12 |    325.12 |  382.54 |
| 2010-01-06 20:44:00 | Withdrawal E |       -80.12 |    245.00 |  302.42 |
| 2010-01-07 04:44:00 | Withdrawal F |      -110.34 |    134.66 |  192.08 |
| 2010-01-07 16:44:00 | Withdrawal G |      -150.25 |    -15.59 |   41.83 |
| 2010-01-08 16:44:00 | Withdrawal H |       -23.90 |    -39.49 |   17.93 |
| 2010-01-08 21:44:00 | Withdrawal I |       -75.66 |   -115.15 |  -57.73 |
| 2010-01-08 22:44:00 | Deposit C    |       275.78 |    275.78 |  218.05 |
| 2010-01-09 11:44:00 | Withdrawal K |       -85.99 |    189.79 |  132.06 |
| 2010-01-09 21:44:00 | Withdrawal J |      -100.00 |     89.79 |   32.06 |
+---------------------+--------------+--------------+-----------+---------+

If your platform does not permit multiple queries per connection, and if you can tolerate the O(N2) inefficiency of a self-join, a self-join does the same job as an accumulating user variable:

SELECT c.id, c.value, d.RunningSum
FROM tbl c
JOIN (
  SELECT a.id, SUM(b.value) AS RunningSum
  FROM tbl a
  LEFT JOIN tbl b ON b.id <= a.id
  GROUP BY a.id
) d USING (id);


Return to the Artful Common Queries page