CODEWITHSUNDEEP
mysqlsqlcumulative-sum
fractal5
fractal5

Reputation: 2132

MySQL - get cumulative sum for multiple entries

I have a table that looks like this. For certain dates, specific values are added (or removed) for label(s).

EntryDate   Label Value
2017-05-01  A     10   
2017-05-03  B     10   
2017-05-05  A     10   
2017-05-05  B     10

I need to get an output that shows me the cumulative sum of values for each day, irrespective of label. The result would look like:

EntryDate   ValueSum
2017-05-01  10   
2017-05-02  10
2017-05-03  20
2017-05-04  20
2017-05-05  40

This query lets me get the cumulative sum for a specific date. Can I run a single query to get the cum. sum for each date?

select EntryDate, sum(value) valueSum from Mytable group by Date

Also, if I'd like to get the sums for only those dates where values have been added/removed, how can I get that?

EntryDate   ValueSum
2017-05-01  10
2017-05-03  20
2017-05-05  40

Thanks.

Upvotes: 1

Views: 1309

Answers (2)

Nigel Ren
Nigel Ren

Reputation: 57121

You can sum it using 

select t.dt, (select sum(value) 
                from Mytable t1 where t1.dt <= t.dt ) 
    from Mytable t

Try and avoid having columns name date as thisis a reserved word and can cause problems. Also this ONLY prints out rows where data is entered, you will have to fill in the gaps programatically.

Upvotes: 1

kapow
kapow

Reputation: 26

This single query which you already provided does give you the sum for each and every date if you leave off the WHERE clause or just make it WHERE true etc.

SELECT `Date`, sum(Value) valueSum FROM `Mytable` GROUP BY `Date`

Date         valueSum
2017-05-08   20
2017-05-10   20
2017-05-11   10

Unfortunately I can't figure out the how to get the sums of just the dates with modifications without more information about the data. Is there another timestamp column that can be checked to see what's recent? Maybe a removal is just flipping a soft delete boolean that can be checked to see what was removed?

Upvotes: 0

Related Questions