CODEWITHSUNDEEP
mysqldatecountgroup-by
Kevin Pei
Kevin Pei

Reputation: 5872

Display empty dates in grouping?

I'm currently using MySQL to count the number of rows created by date for the past week. This is a sample of what my Table looks like:
My Table
With the following SQL query (and considering it's April 20, 2013)

SELECT DATE(Date) AS Date,
       COUNT(id) AS Count
FROM notes
WHERE UNIX_TIMESTAMP(Date) + 604800 >= UNIX_TIMESTAMP()
GROUP BY DAY(Date)
ORDER BY Date ASC

I get the following result.
Output
However, since I'm going through the past week I also want the days where there are no rows like such:

Desired Output

How would I accomplish this?

Upvotes: 1

Views: 120

Answers (1)

a1ex07
a1ex07

Reputation: 37364

I believe the simplest way is 

select a.*, b.`Count`
   FROM 
   (
   select date(now()) as dt 
   union all
   select date(now()-interval 1 day)
   -- then the same for - 2 day, -3 , etc
   )a
   LEFT JOIN 
   (
     --your query
   )b ON (a.dt = b.`date`)

Upvotes: 3

Related Questions