mysql count records within the last month by day and attribute

Question

I'm sure someone can answer this one easy, it is making my already poor brain hurt.

I have a table of users with:

created_date
id
user_type

I am trying to write a query in MYSQL that would count back from today for 30 days and spit back the count of new users added per day (expecting some days to probably be 0) by user_type

Would like a result similar to:

date:      | count(id[user_type = 1]) | count (id[user_type = 2])
2015-02-09           10                      9
2015-02-08            0                      10
2015-02-07            8                      0

and so on....

Thanks!

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html and if you don't have any records for a particular day, then you're not going to get anything for that day. mysql cannot create something out of nothing. — Marc B, Feb 09 '15 at 15:22
fetch the relevant data from the database, create a sparse array of it and present them (for example in HTML tables). — Aitch, Feb 09 '15 at 15:24
As for the creating something from nothing, doesn't count(id) produce 0 if there is nothing that is counted? like if I asked to count(id) where date = curdate(), it would return 0 if there were no records. — ackerchez, Feb 09 '15 at 15:33
There is no "backwards" here. You use `NOW` and `DATE_SUB` to define the interval, [create a list of dates](http://stackoverflow.com/q/510012/785663) and join it to an aggregate view of your user table. — mabi, Feb 09 '15 at 15:37

score 0 · Answer 1 · answered Feb 09 '15 at 15:39

0

use GROUP BY, and look into the time functions http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_day

... GROUP BY MONTH(created_date), DAY(created_date)

answered Feb 09 '15 at 15:39

Alex

5,759
1
32
47

shawnt00 · Answer 2 · 2015-02-09T18:40:31.673

Here's an approach that will work. [http://sqlfiddle.com/#!2/090e19/24]

select
    date_sub(date(now()), interval (d0.n0 + d1.n1 * 5) day),
    count(case when user_type = 1 then 1 else null end),
    count(case when user_type = 2 then 1 else null end)
from
    (select 0 as n0 union all select 1 union all select 2 union all select 3 union all select 4) as d0
    cross join
    (select 0 as n1 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5) as d1
    left outer join users as u on u.created_date = date_sub(date(now()), interval (d0.n0 + d1.n1 * 5) day)
        and u.created_date >= date_sub(date(now()), interval 29 day)
group by date_sub(date(now()), interval (d0.n0 + d1.n1 * 5) day)
order by date_sub(date(now()), interval (d0.n0 + d1.n1 * 5) day) desc;

mysql count records within the last month by day and attribute

2 Answers2