Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
02-27-2014, 12:37 AM #1
- Join Date
- Jun 2008
- Thanked 12 Times in 12 Posts
Help to find the number of event views per day/week/month/year
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
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