CODEWITHSUNDEEP
mysqlsql
Ted
Ted

Reputation: 4166

MySQL: total users in DB aggregated over date

I need to calculate total number of users "aggregated", day by day, ex:

table users:

id    create_date
0     2016-09-01 00:00:00
1     2016-09-01 00:00:00
2     2016-09-01 00:00:00
3     2016-09-02 00:00:00
4     2016-09-02 00:00:00
5     2016-09-02 00:00:00
6     2016-09-03 00:00:00
7     2016-09-03 00:00:00
8     2016-09-04 00:00:00
9     2016-09-04 00:00:00

using the following query:

select date(u.create_date),count(u.id)
from user u
group by date(u.create_date)

returns:

date(u.create_date)    count(u.id)
2016-09-01                 3
2016-09-02                 3
2016-09-03                 2
2016-09-04                 2

but I need to return data aggregated like this:

date(u.create_date)    count(u.id)
2016-09-01                 3
2016-09-02                 6
2016-09-03                 8
2016-09-04                 10

thx,

Note: table key "id" has holes, (Non-sequentially).

Upvotes: 0

Views: 27

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You want a cumulative sum. In MySQL, this is probably easiest using variables:

select dte, cnt, (@c := @c + cnt) as running_cnt
from (select date(u.create_date) as dte, count(u.id) as cnt
      from user u
      group by date(u.create_date)
     ) d cross join
     (select @c := 0) params
order by dte;

Note: When using aggregation with variables, I find that the subquery is necessary.

You could also do:

select d.dte,
       (select count(*)
        from users u
        where u.create_date < date_add(u.dte, interval 1 day)
       ) as running_cnt
from (select distinct date(u.create_date) as dte from user u) d;

For small amounts of data, this is fine performance-wise.

Upvotes: 2

Related Questions