MySql: Select Query- Make A Cumulative Sum Column

Suppose we have a table like following:

 

  
transaction_id
  
acc_no
  
amount
 
 

  
1
  
100001
  
20000
 
 

  
2
  
100002
  
30000
 
 

  
3
  
100003
  
30000
 
 

  
4
  
100004
  
10000
 
 

  
5
  
100005
  
50000
 

And now we want a query that select a cumulative sum column along with other columns like following:

 

  
transaction_id
  
amount
  
cumulative
 
 

  
1
  
20000
  
20000.0
 
 

  
2
  
30000
  
50000.0
 
 

  
3
  
30000
  
80000.0
 
 

  
4
  
10000
  
90000.0
 
 

  
5
  
50000
  
140000.0
 

We can use pl/sql variables like below to generate the above result:

select
    transaction_id,
    amount,
    (@cum_sum:=@cum_sum+amount) as "cumulative"
from
    account
    JOIN (select @cum_sum := 0.0) B;