count how many records `creation` fields are between each of the 24 hours

[jasonc]

I am looking for a way to have mysql count how many records `creation` fields are between each of the 24 hours of the clock.

 

My `creation` field is formatted like this...

 

yyyy-mm-dd hh:mm:ss    (2011-10-26 14:44:17)

 

then have the results something like

 

01 = 234

02 = 10

03 = 9

04 = 41

 

and so on for each of the 24 hours.

 

how could this be done in one query or would i have to run a query for each of the hours ?

[fenway]

You can group by HOUR(yourDateField).

[jasonc]

I see that this groups them, but how can I get it to count the grouped hour segments.

 

 

[jasonc]

Take the following.  I wish to count all the entries that are in each hour segment, so all requests made between 16:00 to 16:59 and show the results, all the requests made between 10:00 to 10:59  and the same for each of the 24 hours.

 

INSERT INTO `requests` (`datetime_added`) VALUES
('2011-04-07 16:10:00'),
('2011-04-07 16:26:00'),
('2011-04-08 10:34:00'),
('2011-04-08 10:37:00'),
('2011-04-08 14:33:00'),
('2011-04-08 16:46:00'),
('2011-04-11 15:49:00'),
('2011-04-13 16:32:00'),
('2011-04-13 16:50:00'),
('2011-04-14 15:25:54'),
('2011-04-14 16:28:04'),
('2011-04-14 19:55:42'),
('2011-04-28 11:33:03'),
('2011-04-28 14:37:34'),
('2011-05-14 13:03:10'),
('2011-10-13 11:25:30'),
('2011-11-03 15:29:56'),
('2011-11-04 12:14:00'),
('2011-11-04 15:32:49'),
('2011-11-05 11:01:57');

[jasonc]

I have messed around in phpmydmin a bit more and come up with the following query that I would like checked over to make sure that it is correct and will do what it should do, or what I am wanting it to do.

 

SELECT `datetime_added` , count( hour( `datetime_added` ) ) AS a
FROM `requests`
GROUP BY hour( `datetime_added` )
ORDER BY hour( `datetime_added` )

[fenway]

Well, the order by is redundant, since group by orders automatically -- and typically you'd use the aggregated value alongside the count, like so:

 

SELECT hour( `datetime_added` ) AS addedHour , count(*) AS cnt
FROM `requests`
GROUP BY hour( `datetime_added` )

 

But they're functionally equivalent.