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.
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);
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
group by clauses.