Single query to get count of individual days of past 7 days, including days with 0 count

Question

SELECT DATE_FORMAT(createdTimestamp, "%D %b") AS date,
       COUNT(id) AS COUNT
FROM registration
WHERE createdTimestamp BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()
GROUP BY DATE(createdTimestamp)

it will show something like

14th Feb - 10
15th Feb - 12
16th Feb - 4
17th Feb - 4
18th Feb - 10
19th Feb - 12
20th Feb - 9

but if there is one day without registration it will skip the day, how do i display it as 0 with a mysql query?

14th Feb - 10
16th Feb - 4
17th Feb - 4
20th Feb - 9

can the query display it to be like the following

14th Feb - 10
15th Feb - 0
16th Feb - 4
17th Feb - 4
18th Feb - 0
19th Feb - 0
20th Feb - 9

If you insert null for any missed reg it wont show problem, Check this [SQL Fiddle](http://www.sqlfiddle.com/#!2/e9e91/2) — Ajo Koshy, Apr 12 '13 at 09:07

score 4 · Accepted Answer · edited May 23 '17 at 11:43

Something like that(you need to generate date table):

SQLFIDDLEExample

select a.Date,
       COALESCE((SELECT cnt
        FROM Table1 t1
        WHERE t1.date = a.Date), 0) as COUNT
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as 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 union all select 8 union all select 9) as a
    cross join (select 0 as 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 union all select 8 union all select 9) as b
    cross join (select 0 as 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 union all select 8 union all select 9) as c
) a
where a.Date between '2013-02-01' and '2013-03-01' 
ORDER BY a.Date

Result:

|                            DATE | COUNT |
-------------------------------------------
| February, 14 2013 00:00:00+0000 |    10 |
| February, 15 2013 00:00:00+0000 |     0 |
| February, 16 2013 00:00:00+0000 |     4 |
| February, 17 2013 00:00:00+0000 |     4 |
| February, 18 2013 00:00:00+0000 |     0 |
| February, 19 2013 00:00:00+0000 |     0 |
| February, 20 2013 00:00:00+0000 |     9 |

Your Query should look like this:

select DATE_FORMAT(a.Date, "%D %b") AS date,
       COALESCE((SELECT COUNT(id)
                 FROM registration
                 WHERE createdTimestamp = a.Date), 0) as COUNT
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as 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 union all select 8 union all select 9) as a
    cross join (select 0 as 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 union all select 8 union all select 9) as b
    cross join (select 0 as 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 union all select 8 union all select 9) as c
) a
where a.Date between DATE_SUB(CURDATE(), INTERVAL 7 DAY) and CURDATE()
ORDER BY a.Date

this is perfect but the sql does not work on mine, im guessing mine has a conflict of the date format. — vinz, Apr 15 '13 at 04:39
createdTimestamp column what datatype is? datetime, date, timestamp — Justin, Apr 15 '13 at 05:25
it worked after i added DATE(createdTimestamp) but i cannot do any other where statement like getting those success status — vinz, Apr 15 '13 at 07:45
apologies i typed it in the wrong statement. it works as long as i cast the createdtimestamp, thanks again justin! — vinz, Apr 15 '13 at 07:51

score 2 · Answer 2 · answered Apr 12 '13 at 09:37

This query create a sequence of 7 numbers (from 0 to 6) and extract the data you need using day differences between curdate and the sequence number:

SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL t.DAY DAY), "%D %b") AS date,
   COUNT(r.id) AS COUNT
FROM
  (SELECT 0 AS 'day'
       UNION ALL SELECT 1
   UNION ALL SELECT 2
   UNION ALL SELECT 3
   UNION ALL SELECT 4
   UNION ALL SELECT 5
   UNION ALL SELECT 6) t
LEFT OUTER JOIN registration r ON DATE_SUB(CURDATE(), INTERVAL t.DAY DAY) = r.createdTimestamp
GROUP BY t.DAY

This is the link to the sqlfiddle

0xCAFEBABE · Answer 3 · 2013-04-12T09:15:33.683

It is not possible with your current query. The query cannot group by something that is not there. You need to generate the missing data.

Select a list of dates you want selected as a table, then OUTER JOIN this to your data by the date and group by the data column from your generated date table.

Edit: You can generate a table of dates to jojn this way (example for 2 days):

select * from (select subdate(current_date,0)) a union (select subdate(current_date,1) b);

Kickstart · Answer 4 · 2013-04-12T09:47:18.510

To get the range of dates you can take a table of integers (from 0 to 9) and join it against itself to get as large a range of numbers as you require, then use DATE_SUB to get the date range from that.

Then LEFT OUTER JOIN that against your current table on the date to get the counts for each date.

Something like the following:-

SELECT DATE_FORMAT(aDate, "%D %b") AS fmtdate, COUNT(registration.id) AS COUNT
FROM (SELECT DATE_SUB(CURDATE(), INTERVAL a.i + b.i*10 DAY) AS aDate
FROM integers a, integers b
WHERE (a.i + b.i*10) <= 7) CountRange
LEFT OUTER JOIN registration
ON aDate = DATE(FROM_UNIXTIME(createdTimestamp))
GROUP BY aDate
ORDER BY aDate

(note that this will cope with up to 99 days - I did the join on integers just to demonstrate how the range can be expanded)

EDIT - the above code is using FROM_UNIXTIME as I made the assumption that your timestamp was stored as a unix timestamp. Easy to remove that function call if my assumption is incorrect

Single query to get count of individual days of past 7 days, including days with 0 count

4 Answers4