gurehbgui gurehbgui - 1 year ago 51
SQL Question

Possible to make a SQL query to count for every hour of a day?

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.

  • First count of entries will be: 000000 til 005959

  • Second will be: 010000 til 015959

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.

Answer Source

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)),
    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)),
    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.