Organizational Research By

Surprising Reserch Topic

cumulative sum over a set of rows in mysql


cumulative sum over a set of rows in mysql  using -'mysql,sql,join,cumulative-sum'

I have a complex query(containing multiple joins, unions) that returns a set of rows containing id, day, hr, amount. The output of the query looks like this:

id day    hr  amount
1   1      1   10       
1   1      2   25       
1   1      3   30        
1   2      1   10       
1   2      2   40       
1   2      2   30        
2   1      1   10       
2   1      2   15        
2   1      3   30       
2   2      1   10       
2   2      2   20      
2   2      2   30  


I need to find cumulative total for each id, for every hour of the day. The output should be like this:

id day    hr  amount cumulative total
1   1      1   10       10
1   1      2   25       35
1   1      3   30       65
1   2      1   10       10
1   2      2   40       50
1   2      2   30       80
2   1      1   10       10
2   1      2   15       25
2   1      3   30       55
2   2      1   10       10
2   2      2   20       30
2   2      2   30       60


My initial query that produces the first output looks like this:

select id, day, hr, amount from
( //multiple joins on multiple tables)a
left join
(//unions on multiple tables)b
on a.id=b.id;


What's sql query to get the cumulative sum as described in the second output? SET should not be used in the solution.

Thanks.
    
asked Oct 11, 2015 by deepak gupta
0 votes
6 views



Related Hot Questions



Government Jobs Opening


...