1

In MySQL I have this query:

SELECT COUNT( * ) AS `total` , DAYOFYEAR( FROM_UNIXTIME( unixtimeissued ) ) AS `dayOfYear` , YEAR( FROM_UNIXTIME( unixtimeissued ) ) AS `year`
    FROM flex_information
    WHERE unixtimeissued >= 'giventime'
    GROUP BY dayOfYear, year
    ORDER BY year, dayOfYear
    LIMIT 0 , 300

This works fine except that I cannot find a way to insert 0 counts where there were no entries for a particular date. i.e. If there are no entries for '6/13/2012' I would like it to return a 'total' of 0 for that date.

EDIT: This data will be used for plotting a line chart so I will need 0 counts paired with the date in order to successfully display an accurate chart - I could fill in the zero values in PHP but a MySQL query would be much less system intensive.

I found this answer: MySQL how to fill missing dates in range?: I now have a 'numbers' table but have been unable to successfully adapt the solution to my query. Some of what I assume is shorthand syntax is making it difficult for me to understand the concepts.

Community
  • 1
  • 1
Luke Nathaniel Abbott
  • 11
  • 2
  • What do you mean by insert 0 counts? If the query returns nothing than there is nothing to insert – Woot4Moo Aug 02 '12 at 14:05
  • Does MySQL have CTE or something similar? If so - check out this solution http://codecorner.galanter.net/2011/08/29/tsql-filling-missing-date-range/ (it is done in TSQL dialect). Even if it doesn't I think you can adapt the solution by using subqueries – Yuriy Galanter Aug 02 '12 at 14:09
  • thank you for your quick responses. @Woot4Moo, I've updated my question in an attempt to clarify. Is this clear now? – Luke Nathaniel Abbott Aug 02 '12 at 14:10
  • @Trekstuff I'll read through the link you've given, maybe it will provide an answer, thank you! – Luke Nathaniel Abbott Aug 02 '12 at 14:12

1 Answers1

0

By virtue of doing the above select you will know exactly when zero results were found. All that should need to occur is the comparison of ranges. For example if you get the following record set back: 

ID     DATE   
1      JUN first    
2      JUN second
3      JUN fifth
4      JUN sixth

It would be clear that JUN third and JUN fourth had no entries so by default they would be zero. It should be possible to find the range of these values through a function.

UPDATE

If the schema is modified like so you can get around the issue: 

ID, DATE, COUNT

By keeping track of the count in the database it is possible to have the number readily available. It should be noted that if many updates are going to occur, as the table grows the process will become time consuming.

Woot4Moo
  • 23,987
  • 16
  • 94
  • 151
  • I could do this in PHP; however, I'm not sure how I can do this in MySQL. MY code is already a bit heavier with PHP than I would like to inflict on my server and I'm hoping to optimize this a bit by using MySQL exclusively for this process to fetch an acceptable set of data rows without need for recursive checks. Is there a way to do this in MySQL that I'm unaware of? Thanks for this answer! – Luke Nathaniel Abbott Aug 02 '12 at 14:27
  • @LukeNathanielAbbott see my updates. Modifying your schema with a count column could resolve the issue. – Woot4Moo Aug 02 '12 at 14:30