4

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.

  • how are you going to determine the 7 day period? Is it grouped by the past 7 days from the day of the query or what? – Taryn Sep 13 '12 at 13:58
  • 1
    Do you want days back 14-8 as 'last week' and days back 7-1 as 'previous week' ? – rfusca Sep 13 '12 at 13:59
  • @bluefeet it will always be 7 days from NOW() & @rfusca it should have said this week & last week, will update the question – cchana Sep 13 '12 at 14:01
12
    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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.