CODEWITHSUNDEEP
mysqlsql
Martin AJ
Martin AJ

Reputation: 6697

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?

Upvotes: 2

Views: 66

Answers (2)

flyingfox
flyingfox

Reputation: 13506

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

Upvotes: 1

nbk
nbk

Reputation: 49375

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

Upvotes: 2

Related Questions