0

I am trying to get average of a column named player_count week by week for the past 6 weeks.

This is the query I am using:

SELECT AVG(`player_count`), `updated_at` FROM `gtan_servers` GROUP BY WEEK(`updated_at`) ORDER BY `updated_at` DESC LIMIT 6

But I also want to get the week interval dates for each of the average that I get. For example, I get by running the above query, I get two averages 96 and 90. What I want is the start and end of the week (dates) in which the above averages were calculated.

Parker Queen
  • 619
  • 2
  • 12
  • 27

1 Answers1

1

Use the below query to get start date and end date of the week

SELECT AVG('player_count'), 'updated_at', updated_at - INTERVAL 
WEEKDAY(updated_at) + 7 DAY as StartDate
, (updated_at - INTERVAL WEEKDAY(updated_at) DAY) - INTERVAL 1 SECOND as 
EndDate FROM 'gtan_servers' GROUP BY WEEK('updated_at') ORDER BY 'updated_at' 
DESC LIMIT 6
Sumit
  • 729
  • 4
  • 9
  • Thanks a lot man. There was a little problem with your query so I modified a little and it worked. SELECT AVG(player_count), updated_at, updated_at + INTERVAL WEEKDAY(updated_at) + 7 DAY as EndDate FROM gtan_servers GROUP BY WEEK(updated_at) ORDER BY updated_at DESC LIMIT 6 – Parker Queen May 25 '17 at 10:31
  • 'updated_at' already works as the start date and adding the interval gives me the end date. – Parker Queen May 25 '17 at 10:32
  • :) Actually my query was getting startdate and enddate of last week, which I was using in my project. I forgot to modify that. Anyways, I am happy to know that your problem is resolved. – Sumit May 25 '17 at 10:37