CODEWITHSUNDEEP
mysqlsqldatabaseselect
galibee
galibee

Reputation: 85

MySQL get record count by hour, even the hours with no records

I want to query for the average of the column values in a table by hour for the past 24 hours. I'm having trouble displaying results even for the hours with no records (in these cases, I should print an average of 0)

Here's what I have so far:

SELECT HOUR(time), AVG(score) FROM place_rvw WHERE time >= NOW() - INTERVAL 1 DAY AND place_id = 1 ORDER BY HOUR(time);

Right now (12 AM), for example, the only records I have is for 9PM and 10PM. Thus, there are only two rows in the result. I want it to have 24 rows (representing each of the past 24 hours) and simply display an average of 0 if an hour has no records.

Any form of help appreciated. Thanks!

Edit I tried the query below like what Stephan suggested, but it still displays the same result. The ideal result should have all 24 rows representing each hour. Any suggestions?

  SELECT za_hours.za_hour as hour, AVG(IFNULL(score,0)) as average_score
  FROM (
  SELECT 0 as za_hour
  UNION
  SELECT 1 as za_hour
  UNION
  SELECT 2 as za_hour
  UNION
  SELECT 3 as za_hour
  UNION
  SELECT 4 as za_hour
  UNION
  SELECT 5 as za_hour
  UNION
  SELECT 6 as za_hour
  UNION
  SELECT 7 as za_hour
  UNION
  SELECT 8 as za_hour
  UNION
  SELECT 9 as za_hour
  UNION
  SELECT 10 as za_hour
  UNION
  SELECT 11 as za_hour
  UNION
  SELECT 12 as za_hour
  UNION
  SELECT 13 as za_hour
  UNION
  SELECT 14 as za_hour
  UNION
  SELECT 15 as za_hour
  UNION
  SELECT 16 as za_hour
  UNION
  SELECT 17 as za_hour
  UNION
  SELECT 18 as za_hour
  UNION
  SELECT 19 as za_hour
  UNION
  SELECT 20 as za_hour
  UNION
  SELECT 21 as za_hour
  UNION
  SELECT 22 as za_hour
  UNION
  SELECT 23 as za_hour
) za_hours
LEFT JOIN place_rvw 
  ON za_hours.za_hour = HOUR(time)
  AND
  time >= NOW() - INTERVAL 1 DAY 
  AND place_id = 1 
GROUP BY
  za_hours.za_hour
ORDER BY za_hours.za_hour

Upvotes: 2

Views: 3140

Answers (4)

Brian DeMilia
Brian DeMilia

Reputation: 13248

Try changing this:

WHERE 
  time >= NOW() - INTERVAL 1 DAY 
  AND place_id = 1

to this:

WHERE 
  (time >= NOW() - INTERVAL 1 DAY
  or time is null)
  AND place_id = 1

Upvotes: 0

jean
jean

Reputation: 4350

1) Create a table with 24 rows, one for each for a hour.

2) Make a left outter join with your table.

The solution WORKS as show in this: SQL fiddle

You are still not getting results due to the filtering

Upvotes: 2

Stephan
Stephan

Reputation: 8090

You need something like :

SELECT 
  za_hours.za_hour, 
  AVG(IFNULL(score,0)) 
FROM (
  SELECT 0 as za_hour
  UNION
  SELECT 1 as za_hour
  ...
  SELECT 23 as za_hour
) za_hours
LEFT JOIN place_rvw 
  ON za_hours.za_hour = HOUR(time)
WHERE 
  time >= NOW() - INTERVAL 1 DAY 
  AND place_id = 1 
GROUP BY
  za_hours.za_hour
ORDER BY za_hours.za_hour

za_hours is a tmp table containing all the hours (0,1.. 24) so that when you have no records for a certain hour you will still have results (even though they will be zero)

Upvotes: 1

Screech129
Screech129

Reputation: 110

Your WHERE statement is keeping it from displaying the rows that contain 0. Use an OR in your WHERE statement to do something like: OR time = 0

Upvotes: 0

Related Questions