Return the count of rows grouped by week MySQL

Question

I am trying to get the row count from a MySQL table where the data is grouped by WEEK.

So far the query I have is:

 "SELECT count(*) as tweets, twitTimeExtracted as date 
  FROM scene.twitData 
  group by week(twitTimeExtracted)"

This query returns the data below:

As you can see, the weeks are not correct, I'm expecting data for each week starting with Monday 7th January (7,14,21,28,4,11 etc...) and running through to this week.

I also tried a modified version of the orignal query:

SELECT count(*) as tweets, twitTimeExtracted as date 
FROM scene.twitData 
WHERE date(twitTimeExtracted) 
   BETWEEN '2013-01-07' and '2013-03-11' 
group by week(twitTimeExtracted)

This returns similar results as the first query.

Maybe there is an inconsistency with some data stored in the DATETIME: twitTimeExtracted column on a few rows of data? I don't really know I'm not very experienced with MySQL.

Any help would really be appreciated.

Thanks

Solution

This converts the datetime value to the appropriate Monday of the week

select count(*) as tweets,
       str_to_date(concat(yearweek(twitTimeExtracted), ' monday'), '%X%V %W') as `date`
from twitData 
group by yearweek(twitTimeExtracted)

yearweek returns both week and year. Together with the string monday, str_to_date gives you the Monday's datetime value.

If your week starts on Monday, use yearweek(twitTimeExtracted, 1) instead.

Comments

[user1814487]

thanks but this kicks up errors: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'day, datediff('7 Jan 2012', twitTimeExtracted)) Mod 7 FROM scene.twitData ' at line 2"

[user1814487]

thanks, this query produces ~19,000 rows of results. the results per week aren't condensed to one result row count.

[sgeddes]

@user1814487 -- not sure why, did you see the SQL Fiddle? Not as expected?

[user1814487]

The SQL fiddle works as expected I believe however when I run the query on my server I'm getting all the results from the week returned with the date as the first day of the month and the time and a 1 in the count(*) column: for example i.imgur.com/kMMIAel.png

[user1814487]

the dates are correct now though starting with Monday 7th January (7,14,21,28,4,11 etc...)

[NateS]

Doesn't this fail when dates are from different years?

[Olaf Dietsche]

@NateS I don't think so. yearweek delivers both year and week, so it should work, even if you have multiple years in your dataset. Please see updated answer.