I have a wallet table like this:

// wallet
 ---- ---------- -------- 
| id | user_id  | amount |
 ---- ---------- -------- 
| 1  | 5        | 1000   |
| 2  | 5        | -200   |
| 3  | 5        | -100   |
| 4  | 5        | 500    |
 ---- ---------- -------- 

I want to make a view that calculates the remaining amount per row. Something like this:

 ---- ---------- -------- ------------------ 
| id | user_id  | amount | remaining_amount |
 ---- ---------- -------- ------------------ 
| 1  | 5        | 1000   | 1000             |
| 2  | 5        | -200   | 800              |
| 3  | 5        | -100   | 700              |
| 4  | 5        | 500    | 1200             |
 ---- ---------- -------- ------------------ 

Any idea how can I do that?

CodePudding user response：

Do not know if this meets your demands or not

SELECT 
  t1.id,t1.user_id,t1.amount,
 (
  SELECT sum(t2.amount) WHERE t2.id<=t1.id AND t1.user_id=t2.user_id
) as remaning_amount
 FROM  yourtable t1

CodePudding user response：

MySQL 8 has window function for that purpose, like SUM() OVER

for your sample data, this will calculate the running SUM for every user_id

vital for th function to work is the PARTITION BY and the ORDER BY to get the right amount

SELECT
`id`, `user_id`, `amount`
, SUM(`amount`) OVER(PARTITION BY `user_id` ORDER BY `id`) run_sum
  FROM wallet

fiddle