CODEWITHSUNDEEP
mysqldatetime
Dave Rau
Dave Rau

Reputation: 103

MySQL: How to count minute rows grouped by day_hour

My select works great except the counts are the same for each hour:

SQLfiddle: http://sqlfiddle.com/#!9/bae01d/32

Player  | Day | Hour | Minutes | % online
player1 |  27 |    0 |      60 |      100
player1 |  27 |    1 |      60 |      100
player1 |  27 |    2 |      60 |      100
player1 |  27 |    3 |      60 |      100
player1 |  27 |    4 |      60 |      100
...

The activity table only has minute by minute entries for players online, offline players will be missing. This is where the join comes in, the stub table contains 24 rows for hours 0-23.

How should this query be rewritten so the total minutes per hour a player was online is the correct total?

select 
    player, 
    day(datetime) as Day,
    stub.hour,
    hour(datetime) as Hour, 
    count(player) as minutes,
    ((count(player) / 60) * 100) as percent_of_hr
 FROM stub_hours as stub
 LEFT JOIN activity
  on Hour=stub.hour
    GROUP BY date_format( datetime, '%Y%m%d%h' ), Day, Hour, player
    ORDER BY player, datetime desc, Day, hour, minutes desc;

Upvotes: 0

Views: 656

Answers (1)

clinomaniac
clinomaniac

Reputation: 2218

I believe this should be what you need: 

SELECT
  sh.player,
  sh.day,
  sh.hour,
  act.count_minutes minutes,
  ((act.count_minutes / 60) * 100) AS percent_of_hr
FROM (SELECT
  player,
  MONTH(datetime) amonth,
  DAY(datetime) aday,
  HOUR(datetime) ahour,
  COUNT(*) count_minutes
FROM activity
GROUP BY player,
         MONTH(datetime),
         DAY(datetime),
         HOUR(datetime)) act
RIGHT JOIN (SELECT DISTINCT
  a.player,
  s.hour,
  DAY(a.datetime) day,
  MONTH(a.datetime) month
FROM activity a
CROSS JOIN stub_hours s) sh
  ON act.ahour = sh.hour
  AND act.player = sh.player
  AND act.aday = sh.day
  AND act.amonth = sh.month
ORDER BY sh.player, sh.day, sh.hour;

http://sqlfiddle.com/#!9/84fc1d/70

Let me know if you need a different output.

Upvotes: 1

Related Questions