GROUP BY with date range

I have a table with 4 columns, id, Stream which is text, Duration (int), and Timestamp (datetime). There is a row inserted for every time someone plays a specific audio stream on my website. Stream is the name, and Duration is the time in seconds that they are listening. I am currently using the following query to figure up total listen hours for each week in a year:

SELECT YEARWEEK(`Timestamp`), (SUM(`Duration`)/60/60) FROM logs_main
WHERE `Stream`="asdf" GROUP BY YEARWEEK(`Timestamp`);

This does what I expect... presenting a total of listen time for each week in the year that there is data.

However, I would lik开发者_如何转开发e to build a query where I have a result row for weeks that there may not be any data. For example, if the 26th week of 2006 has no rows that fall within that week, then I would like the SUM result to be 0.

Is it possible to do this? Maybe via a JOIN over a date range somehow?

The tried an true old school solution is to set up another table with a bunch of date ranges that you can outer join with for the grouping (as in the other table would have all of the weeks in it with a begin / end date).

In this case, you could just get by with a table full of the values from YEARWEEK:

201100
201101
201102
201103
201104

And here is a sketch of a sql statement:

SELECT year_weeks.yearweek , (SUM(`Duration`)/60/60) 

FROM year_weeks LEFT OUTER JOIN logs_main
   ON year_weeks.yearweek = logs_main.YEARWEEK(`Timestamp`)

WHERE `Stream`="asdf" GROUP BY year_weeks.yearweek;

Here is a suggestion. might not be exactly what you are looking for.

But say you had a simple table with one column [year_week] that contained the values of 1, 2, 3, 4... 52

You could then theoretically:

SELECT

A.year_week, 
(SELECT SUM('Duration')/60/00) FROM logs_main WHERE
 stream = 'asdf' AND YEARWEEK('TimeStamp') = A.year_week GROUP BY YEARWEEK('TimeStamp')) 

FROM

tblYearWeeks A 

this obviously needs some tweaking... i've done several similar queries in other projects and this works well enough depending on the situation.

If your looking for a one table/sql based solution then that is deffinately something I would be interested in as well!