Select date and value between two dates with cumulative sum

Question

I'm trying to sum every day between two dates without lose the last value.

A little bit of context:

2020-02-03 have 27 registers
2020-02-04 have 32 registers
2020-02-05 have 28 registers and so on...

So I made this query:

set @CumulativeSum := 0;
select date(t1.created_at), (@CumulativeSum := @CumulativeSum + 
     (select count(*)
     from services_aux t2
     where date(t2.created_at) = date(t1.created_at))) as value
from services_aux t1
where (t1.created_at BETWEEN '2020-02-01' AND '2020-02-07')
group by date(t1.created_at)

The first result is ok, but the sum is wrong. I got:

date       | value
------------------
2020-02-03 | 27
2020-02-04 | 761
2020-02-05 | 1781

But I expect:

date       | value
------------------
2020-02-03 | 27
2020-02-04 | 59
2020-02-05 | 87

Answer 1

If you are running MySQL 8.0, just use window functions, as demonstrated by Tim Biegeleisen.

In earlier versions, user variables are more efficient than a correlated subquery on a large dataset. However they are a bit tricky to use. For one, you don't need a subquery. And a little trick is needed to properly manage the ordering of the sum. I would phrase your query as:

set @cnt := 0;
select created_day, @cnt := @cnt + cnt as value
from (
    select date(t1.created_at) created_day, count(*) cnt
    from services_aux t1
    where t1.created_at between '2020-02-01' and '2020-02-07'
    group by date(t1.created_at)
    order by created_day
) t