Dynamically creating date periods using MySQL

Question

I trying to get the Grape count from dates March 1 - 3.

You will notice that on March 2 - there are no grapes inserted..

I'st possible to show a query from dates March 1, 2 and 3 but showing 0 count for March 2

In this image above only shows dates where there are grapes..

Here is mySQL query

SELECT  `fruitDate` ,  `fruitName` , COUNT( * ) 
FROM  `tbl_fruits` 
WHERE  `fruitName` =  "Grapes"
GROUP BY  `fruitDate

UPDATE 2:

Using this query:

SELECT f.fruitDate, f.fruitName, f1.count FROM tbl_fruits f
    LEFT JOIN (SELECT fruitDate, COUNT(*) as count from tbl_fruits d WHERE d.fruitName='Grapes' GROUP BY d.fruitDate) as f1 ON (f.fruitDate = f1.fruitDate) 
    GROUP BY f.fruitDate

I got this result..but its dsplaying diffrent fruit..something wrong with my query?

Answer 1

March 2 is data that does not exist in your table, what would it select from? That is to say, since count() is counting the number of rows that exist for "Grapes" on each date, and no rows exist for "Grapes" on March 2, count() has no data to count, nothing at all to tell the database to interpolate missing dates.

In order to solve this, what I have done in the past is create a separate table, Calendar, that contains a row for each date for a given range. Then, you can JOIN to this table to assure you select a row for each date. It might look something like this:

SELECT cal.`Date`, 'Grapes' as `fruitName`, COUNT(f.`fruitName`)
FROM `tbl_Calendar` cal 
LEFT JOIN `tbl_fruits` f ON cal.`Date` = f.`fruitDate`  
WHERE `fruitName` = "Grapes"
 AND '2012-03-01' <= cal.`Date` AND cal.`Date` <= '2012-03-03' 
GROUP BY cal.`Date`

Note that count(*) would never return 0 because a row would be returned for each date. To get a 0, count a field that would be NULL when 0 rows are found, in this case, I count fruitName

The Calendar table could be as simple as

CREATE TABLE tbl_Calendar (
  `Date` date NOT NULL PRIMARY KEY
)

Which you would fill with a simple PHP loop from a chosen start date to end date. You may find a benefit in adding other columns to cache things like day-of-week or holidays, but that is not needed for this task.

EDIT

In your edit, you seem to be trying to join back to your fruits table to get dates, but you have some errors in your query, try instead to substitute a similar subquery in place of my Calendar table:

SELECT cal.`Date`, 'Grapes' as `fruitName`, COUNT(f.`fruitName`)
FROM (SELECT `fruitDate` as `Date` FROM `tbl_fruits` GROUP BY `fruitDate`) cal 
LEFT JOIN `tbl_fruits` f ON cal.`Date` = f.`fruitDate`  
WHERE `fruitName` = "Grapes"
GROUP BY cal.`Date`

Note, though, while this will fill in dates missing for Grapes that are not missing for some other fruits, it will not fill in dates which are missing for all fruits.

Answer 2

Remember there is a dynamically (and a bit ugly) solution to creating a date range that does not require creating a table:

select aDate from (
  select @maxDate - interval (a.a+(10*b.a)+(100*c.a)+(1000*d.a)) day aDate from
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) a, /*10 day range*/
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) b, /*100 day range*/
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) c, /*1000 day range*/
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) d, /*10000 day range*/
  (select @minDate := '2001-01-01', @maxDate := '2002-02-02') e
) f
where aDate between @minDate and @maxDate

Depending on the length of the date range you can reduce the amount of dynamically generated results (10000 days means over 27 years of records each representing one day) by removing tables (d, c, b and a) and removing them from the upper formula. Setting the @minDate and @maxDate variables will allow you to specify the dates between you want to filter the results.

Edit:

I see you're still looking for a solution. Try this:

select c.date, f.fruitName, count(f.fruitName = 'Grapes')
from tbl_calendar c
left join tbl_fruits f
on c.date = f.fruitDate and f.fruitName = 'Grapes'
group by c.date, f.fruitName

If you also want to filter the extra dates from the created table, use this query:

select c.date, f.fruitName, count(f.fruitName = 'Grapes')
from tbl_calendar c
left join tbl_fruits f
on c.date = f.fruitDate and f.fruitName = 'Grapes'
group by c.date, f.fruitName
having c.date between
  (select min(fruitDate) from tbl_fruits) and
  (select max(fruitDate) from tbl_fruits)