CODEWITHSUNDEEP
mysql
Glide
Glide

Reputation: 21245

SQL to return record counts in X intervals

I have a table like this:

id    | created_on
1      2013-09-03 20:05:09
2      2013-09-05 17:03:13
...

How do I write a query to return a result of record counts that was created from Date X to Date Y in 7-day intervals?

So the result would look like this:

count | created_on
4       2013-09-17 00:00:00
2       2013-09-24 00:00:00
1       2013-09-31 00:00:00
10      2013-10-07 00:00:00
...

Upvotes: 0

Views: 71

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

You can go to the beginning of the week by subtracting the day of the week. Here is one way to do that:

select date(created_on - interval dayofweek(created_on) day), count(*)
from t
group by date(created_on - interval dayofweek(created_on) day);

If this is not the day you want the week to start, then you can add an offset day.

Upvotes: 3

Shafeeque
Shafeeque

Reputation: 2069

You can try this 

SELECT created_on, count( id ) AS count
FROM `test_table`
WHERE created_on
BETWEEN '2013-09-01'
AND '2013-10-10'
GROUP BY WEEK( created_on )

Upvotes: 1

Nicole
Nicole

Reputation: 33197

Group by the date field, floored to the week:

SELECT
  count(*),
  YEARWEEK(created_on) as week
FROM
  yourtable
GROUP BY week

This assumes that created_on is a type that can be interpreted as a date:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_yearweek

This will get you weekly groupings, but you may want to then convert that field (which will look like YYYYWW) back to something more readable.

Upvotes: 1

Related Questions