Thursday 22 January 2015

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

No comments:

Post a Comment