Make A Cumulative Sum Column In MySql

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 |
+----------------+--------+------------+