My Mysql's table structure is like:
Table name : visitor_sessions_data
------------------------------ date_time | visitor_sessions ------------------------------ 2016-12-01 14:00:00 | 2 ------------------------------ 2016-12-01 18:50:10 | 1 ------------------------------ 2016-12-19 06:00:00 | 1 ------------------------------ 2016-12-20 16:00:00 | 1 ------------------------------ 2017-02-02 19:00:00 | 3 ------------------------------
and so on...
And I want to fetch the data to display in graphs in daily, weekly and monthly order.
I can easily fetch the data to render in chart if daily data is shown. For that I wrote:
SELECT date('date_time'), visitor_sessions
FROM visitor_sessions_data
WHERE (date('date_time')>='2016-12-01') AND (date('date_time')<='2017-02-10')
GROUP BY date('date_time')
So this gives me the data count grouped by each date.
The issue I am facing in calculating the Weekly data as per my own thinking. I am actually unable to build optimized logic for fetching the weekly data from this table.
My requirement is to provide data to chart in following manner:
Week 1: 3,
Week 2: 2,
Week 3: 0,
Week 4: 0,
Week 5: 0,
....
Week X: 3,
Please note: Here week 1 means it should start counting the first date as week 1 (which I provide in mysql), and a week starts from Monday
I tried using this sql but it is only grouping and showing records for weeks where data exists, and doesn't show data for weeks where visitor_sessions are zero:
SELECT DATE_FORMAT(payment_added,'%X-%V'), visitor_sessions
FROM visitor_sessions_data
WHERE (date('date_time')>='2016-12-01') AND (date('date_time')<='2017-02-10')
GROUP BY DATE_FORMAT(payment_added,'%X-%V')
Please help.
Thanks.
I also seek your help in building query for getting grouped data for - monthly - quarterly - yearly
