Summarize data by week and by month

[bonecone]

Is there a way for me to summarize data by week and by month without lumping together the same week or month of two different years?

[Old Pedant]

Sure. Month is trivial:

SELECT Year(datefield) AS theYear, Month(datefield) AS theMongh, SUM(something) AS total, AVG(whatever) AS average
FROM table
GROUP BY Year(datefield), Month(datefield)
ORDER BY Year(datefield), Month(datefield)


Week depends on what your definition of week is. Do weeks ever cross a year boundary??? For example, was 2009-12-27 through 2010-01-02 a week?? If so, it's a little more complex. But if weeks are always in the same year (meaning that there will be 53 or 54 weeks, with as few as 1 day in the first or last week), then it's trivial:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_yearweek