MySql: Select Query- Make A Cumulative Sum Column
create database sam;
use sam;
CREATE TABLE account
(
transaction_id varchar(255),
acc_no varchar(255),
amount int
);
INSERT INTO account (transaction_id, acc_no, amount)
VALUES (1,100,1000);
INSERT INTO account (transaction_id, acc_no, amount)
VALUES (2,100,50);
INSERT INTO account (transaction_id, acc_no, amount)
VALUES (3,101,2000);
INSERT INTO account (transaction_id, acc_no, amount)
VALUES (4,102,211);
select * from account;
+----------------+--------+--------+
| transaction_id | acc_no | amount |
+----------------+--------+--------+
| 1 | 100 | 1000 |
| 2 | 100 | 50 |
| 3 | 101 | 2000 |
| 4 | 102 | 211 |
+----------------+--------+--------+
4 rows in set (0.00 sec)
select
transaction_id,
amount,
(@cum_sum:=@cum_sum+amount) as "cumulative"
from
account
JOIN (select @cum_sum := 0.0) B;
+----------------+--------+------------+
| transaction_id | amount | cumulative |
+----------------+--------+------------+
| 1 | 1000 | 1000 |
| 2 | 50 | 1050 |
| 3 | 2000 | 3050 |
| 4 | 211 | 3261 |
+----------------+--------+------------+