CODEWITHSUNDEEP
mysqldatecount
RaisoMos
RaisoMos

Reputation: 159

mysql - get registred user per week

I'm trying to count registred user per day in one week, i have this sql code and it works fine but it doesn't count users per day.

SELECT  DATE(date_inscrit) date, COUNT(DISTINCT id) totalCount
FROM    Clients
WHERE   DATE(date_inscrit) >= CURDATE() - INTERVAL 7 day
GROUP   BY  DATE(date_inscrit)
order   by id desc

it returns only date where there is registred users.

2015-09-08 => 3 i want to get somthing like this:

Upvotes: 1

Views: 173

Answers (1)

Praveen
Praveen

Reputation: 9335

Try;

select x.dd date, count(distinct id) totalCount
from (
    select CURDATE() - INTERVAL y.a day dd
    from (
        select 0 a union all select 1 union all select 2 union all select 3 union all 
        select 4 union all select 5 union all select 6 union all select 7
    ) y
) x
left join Clients c
on x.dd = date(c.date_inscrit)
group by x.dd
order by c.id desc

Demo sqlfiddle

Upvotes: 1

Related Questions