Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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? –  bluefeet 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
add comment

1 Answer

up vote 10 down vote accepted
    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 ;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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