1

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

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

  • 2015-09-09 => 0
  • 2015-09-08 => 3
  • 2015-09-07 => 0
    ...
RaisoMos
  • 159
  • 4
  • 11
  • 1
    For this you either need a calendar table with all the dates and then a join or need to generate the dates dynamically within the range and then join with the actual query you are using, here is one of my previous answer similar to thins http://stackoverflow.com/questions/23300303/mysql-single-table-select-last-7-days-and-include-empty-rows/23301236#23301236 – Abhik Chakraborty Sep 09 '15 at 09:20
  • ...or handle the logic of missing dates in the presentation layer, if that's available. – Strawberry Sep 09 '15 at 09:29
  • @AbhikChakraborty thank you it's a good idea, but handling it with php arrays can be easier and more comfortable for the database right? – RaisoMos Sep 09 '15 at 09:48
  • Yes unless there is a compulsion of getting things done on DB level you should be using application layer to handle these. – Abhik Chakraborty Sep 09 '15 at 09:56

1 Answers1

1

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

Praveen
  • 8,945
  • 4
  • 31
  • 49