CODEWITHSUNDEEP
phpmysqlsqldatabase
Silver Ringvee
Silver Ringvee

Reputation: 5535

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

Upvotes: 0

Views: 1003

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

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(t.date) 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(t.date) 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 
     ) 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.

Upvotes: 1

Related Questions