Summarize data by week and by month

Discussion in 'Databases' started by bonecone, Jan 9, 2010.

0
  1. #1
    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?
     
    bonecone, Jan 9, 2010 IP
  2. plog

    plog Peon

    Messages:
    298
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    0
    #2
    What you need to do is create a custom field using the DATE_FORMAT function that defines what week/month that record belongs to. For example, if 'fieldd' was a date field in my database and I wanted to make a custom field to group all the months together I would do this with my SQL:

    
SELECT SUM(fielda), DATE_FORMAT(fieldd, '%M %Y') AS datecode FROM yourtable GROUP BY DATE_FORMAT(fieldd, '%M %Y') ORDER BY fieldd;
    PHP:
    The above statement would return something like "October 2009" for datecode and group them all together. To get datecode to appear exactly as you want check out the DATE_FORMAT function:

    http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
     
    plog, Jan 9, 2010 IP