Bearzi Bearzi - 5 months ago 8
SQL Question

MYSQL - Total registrations per day

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-02 4


I tried some sub querying, but somehow i have now idea how to acheive 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

Answer

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