Mysql – Get two weeks of data but group by a period of 7 days

MySQL

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:

For example:

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)

produces:

week 38: 10 result
week 39: 20 results
week 40: 3 results

rather than:

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.

Best Answer

    SELECT 'last week' AS week, COUNT(*) AS rows 
    FROM table_name
    WHERE date_logged >= CURDATE() - INTERVAL  6 DAY 
      AND date_logged  < CURDATE() + INTERVAL  1 DAY
  UNION ALL
    SELECT 'previous week' AS week, COUNT(*) AS rows 
    FROM table_name 
    WHERE date_logged >= CURDATE() - INTERVAL 13 DAY
      AND date_logged  < CURDATE() - INTERVAL  6 DAY ;
Related Question