Summing up values in one column in specific way

user_10800809 0
Viewed 57

So i have a a simple table:

id     amount
 1.      40
 2.      50
 3.      60

I would like to create a query that will return an id number with the sum in a way that first id will have sum 40 , second id will have 40+50=90 , third 40+50+60= 150. Visualized output would be something like this:

id    sum
1.     40
2.     90
3.    150
mysql sql
3 Answers

You can use a correlated subquery :

select t.*,
       (select sum(t1.amount) from table t1 where t1.id <= t.id) as sum
from table t;

If you are working with latest version then you can do :

select t.*,
       sum(amount) over (order by id)
from table t;
user_55294450

Here is a MySQL 8+ option:

SELECT
    id,
    SUM(amount) OVER (ORDER BY id) sum
FROM yourTable;

Demo

This is probably the most performant way to write your query. If you happen to be using an earlier version of MySQL, then use a correlated subquery:

SELECT
    id,
    (SELECT SUM(t2.amount) FROM yourTable t2 WHERE t2.id <= t1.id) AS sum
FROM yourTable t1;
user_18632290

Its very simple to do...

SELECT id, SUM(amount) OVER (ORDER BY id) sum FROM TableName
user_105757820
Related