SQL Select statement Where time is *:00

I'm attempting to make a filtered table based off an existing table. The current table has rows for every minute of every hour of 24 days based off of locations (tmcs).

I want to filter this table into another table that has rows for just 1 an hour for each of the 24 days based off the locations (tmcs)

Here is the sql statement that i thought would have done it...

 SELECT  
 Time_Format(t.time, '%H:00') as time, ROUND(AVG(t.avg), 0) as avg, 
 tmc, Date, Date_Time FROM traffic t 
 GROUP BY time, tmc, Date

The problem is i still get 247,000 rows effected...and according to simple math I should only have:

Locations (TMCS): 14 Hours in a day: 24 Days tracked: 24 Total = 14 * 24 * 24 = 12,096

My original table has 477,277 rows When I make a new table off this query i get right around 247,000 which makes no sense, so my query must be wrong.

The reas开发者_高级运维on I did this method instead of a where clause is because I wanted to find the average speed(avg)per hour. This is not mandatory so I'd be fine with using a Where clause for time, but I just don't know how to do this based off *:00 Any help would be much appreciated

Fix the GROUP BY so it's standard, rather then the random MySQL extension

SELECT  
 Time_Format(t.time, '%H:00') as time,
 ROUND(AVG(t.avg), 0) as avg, 
 tmc, Date, Date_Time
FROM traffic t 
GROUP BY
 Time_Format(t.time, '%H:00'), tmc, Date, Date_Time

Run this with SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY'; to see the errors that other RDBMS will give you and make MySQL work properly