I am looking to run a query that collects the number of results over the last 14 days, this is fairly easy and achieved like so:
SELECT COUNT(id) FROM table_name
What I'd like to do is then group the results so that I can show this week and the previous week. Using the
WEEK() option in MySQL allows me to get the previous two weeks worth of data, but grouping by weeks can return either 2 or 3 results depending on which week of the year the results fall into:
SELECT WEEK(date_logged) as week, COUNT(id) as rows FROM table_name WHERE date_logged > DATE_SUB(NOW(), INTERVAL 14 DAY) GROUP BY WEEK(date_logged)
week 38: 10 result week 39: 20 results week 40: 3 results
this week: 13 results (10 from week 38 and half of week 39) previous week: 20 results (half of week 39 and all of week 40)
Hopefully that dataset illustrates what I'm after.
If possible, I'd like to avoid doing a
select on a
select and hopefully I'm not too far what I'm looking for.