Cumulative sum query on foreign key

Question

I want to write a query for cumulative sum in MYSQL. I have a foreign key in my table and I want to add their hours as a cumulative sum.

Table 1

id(not primary key)   Hours
1                       4
2                       4
1                       5

I have tried this query

select spent_hours := spent_hours + hours as spent 
from time 
join (select spent_hours := 0) s

I am getting this

id(not primary key)    hours    spent
1                       4        4
2                       4        8
1                       5        13

But I want this result:

id(not primary key)    Hours spent
1                       4    4
2                       4    4
1                       5    9

select spent_hours := spent_hours + hours as spent from time join (select spent_hours := 0) s — Jerry, Jul 21 '15 at 00:05
Look at [this answer](http://stackoverflow.com/a/1290936/2055998) for correct syntax using MySQL variables. — PM 77-1, Jul 21 '15 at 00:08
Would it be OK to have `spend` field be the user cumulative and user not running cumulative? so `1-4-8` `2-4-4` `1-4-8`? — Aziz Saleh, Jul 21 '15 at 00:14
Also how hard set are you on the table structure? Would you be able to add an autoincrement field? — Aziz Saleh, Jul 21 '15 at 00:18

Aziz Saleh · Accepted Answer · 2015-07-21T00:22:54.367

1

Since you have an autoincrement field (let's assume for this case its called record_id) you can use this little trick to achieve what you want:

SELECT Main.id, Main.spentHours,
(
    SELECT SUM(spentHours) 
    FROM Table1 WHERE Table1.id = Main.id
    AND Table1.record_id >= Main.record_id
) as totalSpentHours
FROM Table1 Main
ORDER BY Main.record_id ASC

This will fetch the id, current spent hours, along using a subselect, all hours from the current ID and above for that user.

edited Jul 21 '15 at 00:22

answered Jul 21 '15 at 00:08

Aziz Saleh

2,687
1
17
27

Based on the expected result, OP wants **running total**. – PM 77-1 Jul 21 '15 at 00:09

score 0 · Answer 2 · answered Jul 21 '15 at 00:26

You need additional an variable to keep track of the cumulative sum within each id:

select t.id, t.hours,
       (@h := if(@i = id, @h + spent_hours,
                 if(@i := id, spent_hours, spent_hours)
                )
       ) as spent
from time cross join
     (select @h := 0, @i := 0) params
order by id, ??;

Note: you need an additional column to specify the order for the cumulative sum (indicated by ?? in the order by clause. Remember that SQL tables represent unordered sets, so you need a column to explicitly represent ordering.

I'm not convinced it's 'needed' (the variable, not the key) but it is a great solution. — Strawberry, Jul 21 '15 at 01:01

Cumulative sum query on foreign key

2 Answers2