1

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.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
fractal5
  • 2,034
  • 4
  • 29
  • 50
  • your `select Date, sum(value) valueSum from Mytable group by Date` seems to already meet objective 1. Objective 2 would require more details on how you denote values which have been added/removed. – xQbert May 09 '17 at 19:56
  • No my query only returns the sum (not cumulative) of the values for each date. I'm looking to get the cumulative sum for each date. – fractal5 May 09 '17 at 19:59
  • then date is a date time not a date and you need to cast it to just a date. `select date(Date), sum(value) valueSum from Mytable group by date(Date)` – xQbert May 09 '17 at 20:05
  • I'm not sure what you mean. I think there's some confusing with me using Date as a column name. I have updated it to EntryDate. – fractal5 May 09 '17 at 20:09
  • Look at ths tread: https://stackoverflow.com/questions/14105018/generating-a-series-of-dates – Michael May 09 '17 at 20:19
  • Possible duplicate of [Creating a cumulative sum column in MySQL](https://stackoverflow.com/questions/7629200/creating-a-cumulative-sum-column-in-mysql) – Lukas Eder Sep 17 '18 at 18:06

2 Answers2

1

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.

Nigel Ren
  • 56,122
  • 11
  • 43
  • 55
0

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?

kapow
  • 26
  • 3