Silver Ringvee Silver Ringvee - 2 months ago 17
SQL Question

MySql Select and count number of data per day - include empty dates

How to select & group data from the database by day, but also include days without an entry. In that case, the value would be 0.

If not doable with MySQL, could also use PHP.

Here's what I currently have:

SELECT DATE_FORMAT(date, '%Y-%m-%d') AS the_date
, COUNT(*) AS count
FROM table
WHERE date BETWEEN DATE_FORMAT('2016-11-28', '%Y-%m-%d') AND DATE_FORMAT('2016-12-05', '%Y-%m-%d')
GROUP
BY the_date

Answer

It might be simpler to add the missing dates in PHP. In MySQL, you need to get the dates from somewhere. Often, databases have a "calendar" table with this information. If you have one, then something like this does what you want:

SELECT DATE_FORMAT(c.date, '%Y-%m-%d') AS the_date, COUNT(*) AS count 
FROM Calendar c LEFT JOIN
     table t
     ON c.date = t.date
WHERE c.date BETWEEN '2014-11-28' AND '2014-12-05' -- MySQL recognizes strings in the format of YYYY-MM-DD as dates when needed
GROUP BY the_date;

If you don't have such a table, you can make one for the query:

SELECT DATE_FORMAT(c.date, '%Y-%m-%d') AS the_date, COUNT(*) AS count 
FROM (SELECT date('2014-11-28') as date UNION ALL
      SELECT date('2014-11-29') as date UNION ALL
      SELECT date('2014-11-30') as date UNION ALL
      SELECT date('2014-12-01') as date UNION ALL
      SELECT date('2014-12-02') as date UNION ALL
      SELECT date('2014-12-03') as date UNION ALL
      SELECT date('2014-12-04') as date UNION ALL
      SELECT date('2014-12-05') as date UNION ALL
     ) c LEFT JOIN
     table t
     ON c.date = t.date
GROUP BY the_date;

This works for a small number of dates but is not going to work so well for ranges that span months or years.