I'm stuck, this job seems too complicated for me I hope in some goodness help here!
So this is my table views
:
+---+--------------------+
| id| time|
+---+--------------------+
| 1 | 2016-10-06 10:13:11|
| 2 | 2016-10-07 15:33:31|
| 3 | 2016-10-08 20:13:13|
| 4 | 2016-10-09 09:16:21|
| 5 | 2016-10-10 18:23:17|
| . | ...|
+---+--------------------+
I need to select in array the rows count of each day for the last 2 weeks and compare them (so I need to compare monday of current week and previous week).
So I tried:
SELECT 'last week''last_week' AS week, COUNT(*) AS rows
FROM `views`
WHERE `time` >= CURDATE() - INTERVAL 6 DAY
AND `time` < CURDATE() + INTERVAL 1 DAY
UNION ALL
SELECT 'previous week''previous_week' AS week, COUNT(*) AS rows
FROM `views`
WHERE `time` >= CURDATE() - INTERVAL 13 DAY
AND `time` < CURDATE() - INTERVAL 6 DAY;
But it counts rows of each weeks and not single days.
Yes, I can add eg. AND DAYOFWEEK(
time) = 7;
to the query, but in this case I have to declared it for seven times.
Basically I need something like:
+---------------+--------------------+
| week | 1| 2| 3| 4| 5| 6| 7|
+---------------+--+--+--+--+--+--+--+
| last_week | 1| 5| 3|15|12| 1| 3|
| previous_week |10|15|34|22|55|78|99|
+---------------+--+--+--+--+--+--+--+
Where 1 is Monday, 7 Sunday.