Count MySQL-Entries per day, inclusive days without any entries (Date Range)

Question

I have a MySQL-Table

id     mydate         content
----------------------------------
1      2015-06-20     some content
2      2015-06-20     some content
3      2015-06-22     some content

Now I want to count the entries for each day:

SELECT DATE(mydate) Date, COUNT(DISTINCT id) dayCount FROM mytable
GROUP BY DATE(mydate) HAVING dayCount > -1 ORDER BY DATE(mydate) DESC

This works for me, result:

2015-06-20 = 2
2015-06-22 = 1

How can I fetch days without any entries? In my example the result should be:

2015-06-19 = 0
2015-06-20 = 2
2015-06-21 = 0
2015-06-22 = 1
2015-06-23 = 0

Based on this:

<?php
$today = date("Y-m-d");
$mystartdate = date_create($today);
date_sub($mystartdate, date_interval_create_from_date_string('14 days'));
$mystartdate = date_format($mystartdate, 'Y-m-d');
?>

Finaly I want to output the counts of the last 14 days, also with "0-days". Hope you understand my problem.

Sorry, I don't know what you mean. I'm sure that is possible to put the MySQL-Statement into a _php-script_. `while($date > $startdate) { // SQL-Statement }` but this can't be a good solution - this can't be good for the performance. — Christoph, Jun 23 '15 at 11:24
No. You don't loop the query. You loop the result. There's so many examples of this kind of thing out there, that it seems silly to provide yet another example. — Strawberry, Jun 23 '15 at 11:27

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

For this you can create new table that holds the increment numbers, but it's not a great idea. However, if doing it this way, use this table to construct a list of dates using DATE_ADD.

LEFT JOIN onto your table of data based on the time portion to achieve your list of dates

for more info go through the link

MySQL how to fill missing dates in range?

edited May 23 '17 at 12:05

Community

1
1

answered Jun 23 '15 at 11:26

Santosh Jagtap

995
8
17

score 0 · Answer 2 · answered Jun 23 '15 at 11:28

try below-

SELECT a.date_field, COUNT(DISTINCT b.id) dayCount FROM 
(SELECT date_field FROM
(
    SELECT
        MAKEDATE(YEAR(NOW()),1) +
        INTERVAL (MONTH(NOW())-1) MONTH +
        INTERVAL daynum DAY date_field
    FROM
    (
        SELECT t*10+u daynum
        FROM
            (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
            (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
            UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
            UNION SELECT 8 UNION SELECT 9) B
        ORDER BY daynum
    ) AA
) AAA
WHERE MONTH(date_field) = MONTH(NOW()) ) a 
LEFT JOIN mytable b ON a.date_field=DATE(b.mydate) 
GROUP BY a.date_field HAVING dayCount > -1 ORDER BY a.date_field DESC;

Count MySQL-Entries per day, inclusive days without any entries (Date Range)

2 Answers2