· 5 years ago · Jun 26, 2020, 03:38 PM
1DROP TABLE IF EXISTS transactions;
2CREATE TABLE transactions(
3 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
4 tgl DATE,
5 amount INT null
6) ENGINE=MyISAM;
7
8INSERT INTO transactions VALUES
9(1,'2020-05-30',5200),
10(2,'2020-05-31',1500),
11(3,'2020-06-01',3200),
12(4,'2020-06-02',3500),
13(5,'2020-06-03',1200),
14(6,'2020-06-04',5200),
15(7,'2020-06-05',7300),
16(8,'2020-06-06',3400),
17(9,'2020-06-07',4900),
18(10,'2020-06-08',8600),
19(11,'2020-06-09',1700),
20(12,'2020-06-10',5500);
21
22 select * from transactions;
23+----+------------+--------+
24| id | tgl | amount |
25+----+------------+--------+
26| 1 | 2020-05-30 | 5200 |
27| 2 | 2020-05-31 | 1500 |
28| 3 | 2020-06-01 | 3200 |
29| 4 | 2020-06-02 | 3500 |
30| 5 | 2020-06-03 | 1200 |
31| 6 | 2020-06-04 | 5200 |
32| 7 | 2020-06-05 | 7300 |
33| 8 | 2020-06-06 | 3400 |
34| 9 | 2020-06-07 | 4900 |
35| 10 | 2020-06-08 | 8600 |
36| 11 | 2020-06-09 | 1700 |
37| 12 | 2020-06-10 | 5500 |
38+----+------------+--------+
39
40
41SELECT x.tgl
42 , x.amount
43 , SUM(y.amount) AS accu
44FROM
45 (
46 SELECT *
47 FROM transactions
48 ) x
49 JOIN
50 (
51 SELECT *
52 FROM transactions
53 ) y
54 ON (y.tgl <= x.tgl AND DATE_FORMAT(y.tgl,'%Y%m')=DATE_FORMAT(x.tgl,'%Y%m'))
55 GROUP
56 BY x.tgl,x.amount;
57
58+------------+--------+-------+
59| tgl | amount | accu |
60+------------+--------+-------+
61| 2020-05-30 | 5200 | 5200 |
62| 2020-05-31 | 1500 | 6700 |
63| 2020-06-01 | 3200 | 3200 |
64| 2020-06-02 | 3500 | 6700 |
65| 2020-06-03 | 1200 | 7900 |
66| 2020-06-04 | 5200 | 13100 |
67| 2020-06-05 | 7300 | 20400 |
68| 2020-06-06 | 3400 | 23800 |
69| 2020-06-07 | 4900 | 28700 |
70| 2020-06-08 | 8600 | 37300 |
71| 2020-06-09 | 1700 | 39000 |
72| 2020-06-10 | 5500 | 44500 |
73+------------+--------+-------+