MYSQL - Total registrations per day

Question

I have the following structure in my user table:

id(INT)    registered(DATETIME)
1          2016-04-01 23:23:01
2          2016-04-02 03:23:02
3          2016-04-02 05:23:03
4          2016-04-03 04:04:04

I want to get the total (accumulated) user count per day, for all days in DB So result should be something like

day          total
2016-04-01   1
2016-04-02   3
2016-04-03   4

I tried some sub querying, but somehow i have now idea how to achieve this with possibly 1 SQL statement. Of course if could group by per day count and add them programmatically, but i don't want to do that if possible.

Feel free to share what you tried. And any reason to avoid `GROUP BY`? — 1000111, Jun 23 '16 at 05:17

score 1 · Accepted Answer · answered Jun 23 '16 at 06:30

You can use a GROUP BY that does all the counts, without the need of doing anything programmatically, please have a look at this query:

select
  d.dt,
  count(*) as total
from
  (select distinct date(registered) dt from table1) d inner join
  table1 r on d.dt>=date(r.registered)
group by
  d.dt
order by
  d.dt

the first subquery returns all distinct dates, then we can join all dates with all previous registrations, and do the counts, all in one query.

An alternative join condition that can give some improvements in performance is:

on d.dt + interval 1 day > r.registered

score 0 · Answer 2 · answered Jun 23 '16 at 06:21

Not sure why not just use GROUP BY, without it this thing will be more complicated, anyway, try this;)

select
    date_format(main.registered, '%Y-%m-%d') as `day`,
    main.total
from (
    select
        table1.*,
        @cnt := @cnt + 1 as total
    from table1
    cross join (select @cnt := 0) t
) main
inner join (
    select
        a.*,
        if(@param = date_format(registered, '%Y-%m-%d'), @rowno := @rowno + 1 ,@rowno := 1) as rowno, 
        @param := date_format(registered, '%Y-%m-%d')
    from (select * from table1 order by registered desc) a
    cross join (select @param := null, @rowno := 0) tmp
    having rowno = 1
) sub on main.id = sub.id

SQLFiddle DEMO

MYSQL - Total registrations per day

2 Answers2