Question 283759 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Group Average Player Count by Hour

I would like to gather the averages for player-count for each hour of the day so we can get a general idea of when players are most on. The data is stored in a MySQL data.

My table is as follows:

id   |   datetime               |  player-count  | players
1    |   2023-11-10 23:10:05    |    32          | [{jsondata}]
1    |   2023-11-10 23:15:02    |    10          | [{jsondata}]
1    |   2023-11-10 23:20:05    |    25          | [{jsondata}]
1    |   2023-11-10 23:25:02    |    9           | [{jsondata}]
1    |   2023-11-10 23:30:01    |    3           | [{jsondata}]

I am unsure of how to construct the MySQL query. I tried something along

SELECT *,AVG(`player-count`) AS average from `".$db->prefix."server_player_count` GROUP BY CAST(`datetime` as DATE), DATEPART(Hour, StartDate) ORDER BY CAST(`datetime` as DATE) ASC;");

But doesn’t seem to work. Anyone point me in the right direction.

Tags:

2

Answers


  1. 0

    How about something like this:

    SELECT 
  DATE(`datetime`) AS `date`,
  HOUR(`datetime`) AS `hour`,
  AVG(`player-count`) AS `average` 
FROM `server_player_count` 
GROUP BY `date`, `hour` 
ORDER BY `date` ASC, `hour` ASC;

    Demo: http://sqlfiddle.com/#!9/99d103/1

    It looks very much like what you suggested, so what went wrong? Why did you ask the question?

    Login or Signup to reply.
  2. 0 
    select *,avg(player_count) from table group by hour,date

    so here you are not adding rest of the columns in group by condition as you are mentioning * in select statement. so this is throwing error(this is my assumption)

    below is the query which will work to get the player_count day and hour wise

    **SELECT 
  AVG([player-count]) AS average,DATEPART(Hour, [datetime]) as hour,CAST([datetime] as DATE) as date from [server_player_count]
  GROUP BY CAST([datetime] as DATE), DATEPART(Hour, [datetime]) ORDER BY CAST([datetime] as DATE) ASC;**
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search