Mysql Select Count For Every Date In Month

Let's say I have a table like the following:

date_upload|url_upload|status_upload
2017-11-01 |www.com   |verified
2017-12-01 |www.com   |verified
2017-13-01 |www.com   |verified
2017-11-01 |www.com   |verified

and I want result from count like this:

date_upload |count_upload
2017-01-01  | 0
2017-02-01  | 0
..........  | 0
2017-11-01  | 2
2017-12-01  | 1
2017-13-01  | 1
2017-14-01  | 0
..........  | 0
2017-31-01  | 0

The result should return a count of every date in this month, although mine records just 4.

1 answer

  • answered 2018-01-14 11:05 Tim Biegeleisen

    Use a calendar table:

    SELECT t1.date, COUNT(t2.date_upload) AS count_upload
    FROM
    (
        SELECT '2017-01-01' AS date UNION ALL
        SELECT '2017-01-02' UNION ALL
        SELECT '2017-01-03' UNION ALL
        ...
        SELECT '2017-01-31'
    ) t1
    LEFT JOIN yourTable t2
        ON t1.date = t2.date_upload
    GROUP BY
        t1.date;
    

    In practice, the subquery aliased as t1 can be replaced with a bona fide calendar table containing those dates.