CODEWITHSUNDEEP
mysqlsql
true_H
true_H

Reputation: 85

Summing up values in one column in specific way

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

Upvotes: 4

Views: 60

Answers (3)

Sahil Anand
Sahil Anand

Reputation: 139

Its very simple to do...

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

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;

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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;

Upvotes: 4

Related Questions