Hi All

I have the following query

SELECT view_id, user_id, event_id, date_viewed, COUNT( event_id ) AS views
FROM `tbl_event_views`
WHERE user_id =1
GROUP BY event_id
LIMIT 0 , 30
which returns the total number of views each event has, but what is to have a break down of the number of views to show views:
per day
per week
per month
per year

but i dont know where to start. I'm guessing i will need 4 seperate queries one for each of the above?

The hardest part to get my head around is say i have a listing which started on 3rd Jan 2014 and its now the 26th Feb
  • the per day will need to return a value for every day from then to now (3rd Jan - 26th Feb)
  • the per week will need to return values every 7 days from then till now (3rd - 9th Jan, 10th - 16th Jan, 17th - 23th Jan, 24th - 31st Jan, 1st - 7th Feb, 8th - 14th Feb, 15th - 21st Feb, 22nd - 26th Feb)
  • the per month, should i stick to jan feb months or have it at 4 week intevals from the start date? if it were from the start date (3rd Jan - 3rd Feb, 4th Feb - 4th March)
  • the per year would be (3rd Jan 2014 - 3rd Jan 2015)

hope that makes sence? really not sure where to begin with this, any help would be greatly appreciated

Many Thanks