0

I have the following table 

   id_val date_val    volume_val
    1   26-Jan-18   100
    1   25-Jan-18   200
    1   24-Jan-18   300
    1   23-Jan-18   150
    2   26-Jan-18   250
    2   25-Jan-18   350
    2   24-Jan-18   400
    3   23-Jan-18   500

I need to estimate the cumulative volume by id . This is what i want to get 

id_val date_val    volume_val     cumulative_volume_val
1   26-Jan-18       100             100
1   25-Jan-18       200             300
1   24-Jan-18       300             600
1   23-Jan-18       150             750
2   26-Jan-18       250             250
2   25-Jan-18       350             600
2   24-Jan-18       400             1000
3   23-Jan-18       500             500

Here is what I tried but it doesnt work . it sums up everything and does not split by groups. How can I improve it ? 

set @csum := 0;  

select id_val, date_val,   
(@csum := @csum + volume_val) as cumulative_volume      
from temp  
group by id_val, date_val    
order by id_val, date_val desc
john
  • 647
  • 5
  • 23
  • 53
  • You have no aggregating functions so your group by clause is unwarranted – Strawberry Jan 28 '18 at 11:07
  • For further help see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Jan 28 '18 at 11:08

1 Answers1

1

Here is a solution I adapted from the answer to a similar question here: Does mysql have the equivalent of Oracle's "analytic functions"?

select id_val, date_val,   
(case when @id_val != id_val then @sum := volume_val else @sum := @sum + volume_val end) as cumulative_volume,
(case when @id_val != id_val then @id_val := id_val else @id_val end) as _
FROM (SELECT * FROM temp ORDER BY id_val, date_val desc) z
JOIN (SELECT @sum := 0) s
JOIN (SELECT @idval := -1) i
Mark Maurice Williams
  • 217
  • 2
  • 5