MYSQL Group按天数总和每天

Question

我有一个查询，我在一个范围内每天运行多次。我想这减少到单个查询：

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

我遇到一个问题，试图将其转换成一个单一的查询 - 我不能GROUP BY“created_at”因为我之前拍摄的所有费用和日期。 （不只是那天）。不知道这是否可能，但我敢肯定，如果这里有一位天才能解决这个问题。注意：如果我可以为每一行返回myDate以及总和也会很好。

下面是我在做什么一个完整的例子：

$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; 
} 

所以最好我想运行可以用日期范围或天间隔的一个日期和数量，工作单查询有它吐出像 行：

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

Answer 1

我觉得下面的查询表示你在找什么。根据需要，它可在日期范围上运行。这是一个生成累计和的单个查询（从日期范围中给出的开始日期总结）。如果你想从时代开始完成总结，那也可以完成。

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:[email protected]+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 | 

如果你想获得累加和从时代开始，...

set @csum := 0; 
select date_format(dt,'%Y-%m-%d') dt,@csum:[email protected]+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 |