MYSQL Group By Interval Of Days Sum Each Day

Question

I have a query that I am running multiple times for each day over a range. I would like to reduce this to a single query:

SELECT SUM(amount) AS all_time_revenue
FROM charges WHERE DATE(`charges`.`created_at`) <= '2015-03-01'

I'm running into a problem trying to convert this into a single query - I can't GROUP BY "created_at" because I am capturing all charges before and on the date. (Not just on that date). Not sure if this is possible, but I'm sure if it is there is a genius on here that can solve it. Note: It would also be nice if I could return the myDate as well as the sum for each row.

Here is a complete example of what I'm doing:

$start_time = new DateTime('2016-08-01');
$end_time = new DateTime('2016-08-14');
$data = [];
for ($start_time; $start_time < $end_time; $start_time = $start_time->modify('+1 day')) {
     $date = $start_time->format("Y-m-d");

     $results = DB::SELECT("SELECT SUM(amount) AS all_time_revenue
                            FROM charges 
                            WHERE `charges`.`created_at` <= ? AND `charges`.`sandbox`=0",
                            ", [$date]);
     $data[$date] = $results[0]->all_time_revenue;
}

So ideally I would like to run a single query that can work with a date range or a single date and number of days interval and have it spit out rows like:

row 1: ['2016-08-01', 4000.00]
row 2: ['2016-08-02', 4500.00]
...
row 14:['2016-08-14', 15000.00]

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Sep 14 '16 at 21:39
It's not all clear what the datatype of `created_at` is... is that a DATE? a DATETIME? And what range of values is being provided for $mydate? — spencer7593, Sep 14 '16 at 21:44
Thanks, because I had no idea what @Strawberry was complaining about. — Jon Robinson, Sep 14 '16 at 21:47
I think you are looking for cumulative sums grouped by date. Look [here](http://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql) — blackpen, Sep 14 '16 at 21:56

blackpen · Answer 1 · 2016-09-15T16:13:33.807

I think the following query represents what you are looking for. It works on a date range as you needed. It is a single query that produces the cumulative sum (sums it up from the starting date given in the date range). If you want the summing to be done starting from epoch, that also could be done.

create table tt (dt date, i integer);

insert into tt values("2016-01-01",1);

insert into tt values("2016-01-02",1);
insert into tt values("2016-01-02",2);

insert into tt values("2016-01-03",1);
insert into tt values("2016-01-03",2);
insert into tt values("2016-01-03",3);

insert into tt values("2016-01-04",1);
insert into tt values("2016-01-04",2);
insert into tt values("2016-01-04",3);
insert into tt values("2016-01-04",4);

insert into tt values("2016-01-05",1);
insert into tt values("2016-01-05",2);
insert into tt values("2016-01-05",3);
insert into tt values("2016-01-05",4);
insert into tt values("2016-01-05",5);

insert into tt values("2016-01-06",1);
insert into tt values("2016-01-06",2);
insert into tt values("2016-01-06",3);
insert into tt values("2016-01-06",4);
insert into tt values("2016-01-06",5);
insert into tt values("2016-01-06",6);

set @csum := 0;
select date_format(dt,'%Y-%m-%d') dt,@csum:=@csum+i as csum
from (
   select dt,sum(i) as i
   from tt 
   where dt between date('2016-01-02') and date('2016-01-05')
   group by dt
) as t1
order by 1;

|         dt | csum |
|------------|------|
| 2016-01-02 |    3 |
| 2016-01-03 |    9 |
| 2016-01-04 |   19 |
| 2016-01-05 |   34 |

If you want to get cumulative sums starting from epoch, ...

set @csum := 0;
select date_format(dt,'%Y-%m-%d') dt,@csum:=@csum+i as csum
from (
   select dt,sum(i) as i
   from tt 
   where dt<=date('2016-01-05')
   group by dt
) as t1
where dt<=date('2016-01-05') and date('2016-01-05')
order by 1;

|         dt | csum |
|------------|------|
| 2016-01-01 |    1 |
| 2016-01-02 |    4 |
| 2016-01-03 |   10 |
| 2016-01-04 |   20 |
| 2016-01-05 |   35 |

Thanks again, but I believe it's still a little off. Given your data above, and the date range. 2016-01-03 -> 2016-01-05. I would want to return the sums as follows: `['2016-01-03' => 10, '2016-01-04' => 20, '2016-01-05' => 35]` — Jon Robinson, Sep 15 '16 at 18:05
I'm thinking I can do this by breaking it down to 2 queries. For example for the date range of **2016-01-03 -> 2016-01-05**: The first would get the total charges on and before **2016-01-03**. The second would use your cumulative sum technique, for **2016-01-04 - 2016-01-05** and use a little PHP to append the correct totals. This would still be a lot more efficient than the way I was doing it over large date ranges. — Jon Robinson, Sep 15 '16 at 18:36
If you have dependencies, which could be better satisfied by breaking it into two queries, that is fine. You are going to compute the sum till 2016-01-03 using first query; then use that value as bootstrap for computing cumulative sums in the second query. I think you are headed in right direction. — blackpen, Sep 15 '16 at 19:46

MYSQL Group By Interval Of Days Sum Each Day

1 Answers1