I have an SQL Table which stores some data and a date (e.g. yyyymmdd) and a time (e.g. hhmmss). Now I need to select a count of the number of entries for every hour of the day.
Is it possible to make this with a single select and then have an array of 24 count results? Otherwise I will need to create 24 selects to get all the data I want.
Use group by function. Another option is to create fields for hour, minutes and seconds. It will duplicate data, but there is no problem.
CREATE TABLE test(id int, date varchar(8), time varchar(6)); INSERT INTO test VALUES(1,'20160616','015959'); INSERT INTO test VALUES(2,'20160616','015959'); INSERT INTO test VALUES(3,'20160616','025959'); SELECT count(substr(time,1,2)), test.date FROM test GROUP BY date, substr(time,1,2); Output:
count(substr(time,1,2)), date '2', '20160616' '1', '20160616' If you have a TIME column, just use:
SELECT count(hour(time)), test.date FROM test GROUP BY date, hour(time); If you don't need to have this count by date, just remove from select and group by clauses.
I think you can just use the HOUR function. I don't use MYSQL, but it looks like it has this feature. Might check the syntax on it.
SELECT HOUR(TheTimeField), COUNT(*) FROM TheTable GROUP BY HOUR(TheTimeField) ORDER BY HOUR(TheTimeField) 
select hour (time_column), count (*) from table group by hour(time_column) http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_hour
