Question 113298 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to calculate the remaining amount per row?

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?

Tags:

2

Answers


  1. 0

    Do not know if this meets your demands or not

    SELECT 
  t1.id,t1.user_id,t1.amount,
 (
  SELECT sum(t2.amount) FROM yourtable t2 WHERE t2.id<=t1.id AND t1.user_id=t2.user_id
) as remaning_amount
 FROM  yourtable t1
    Login or Signup to reply.
  2. 0

    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

    The PARTITION BY is used to get sums for a user_id, so if you had user 5,6,7,8 it will correctly add (or subtract) the maount theat that user produced.

    The ORDER BYis needed to get the right mount at the corect position. Tables are by nature unsortede, so an ORDER BY is needed to give the outout the corect order, if the ids where changed, you would get another mount, as it will be prior added to the running sum

    SELECT
`id`, `user_id`, `amount`
, SUM(`amount`) OVER(PARTITION BY `user_id` ORDER BY `id`) run_sum
  FROM wallet
    id user_id amount run_sum
    1 5 1000 1000
    2 5 -200 800
    3 5 -100 700
    4 5 500 1200

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search