Combining multiple mySQL values

Question

I have 7 states and 11 time intervals from 09:00 to 20:00.

I have a table:

id | name | time                | state
________________________________________
1  | aaaa | 2018-03-06 09:10:13 | NY
2  | bbbb | 2018-03-06 11:15:10 | LA
3  | cccc | 2018-03-06 13:02:11 | LA
4  | dddd | 2018-03-06 11:06:22 | NY
5  | eeee | 2018-03-06 09:33:17 | NY
6  | ffff | 2018-03-06 09:16:43 | LA
7  | gggg | 2018-03-06 17:08:36 | LA
8  | hhhh | 2018-03-06 14:25:47 | NY
9  | iiii | 2018-03-06 17:02:33 | LA

Here I have to repeat the same request for each possible state and hour from 09:00 to 20:00 (7 states x 11 time intervals = 77 requests):

SELECT COUNT(*) FROM table WHERE state = "NY" AND time LIKE "% 09:%"

Now I want to get the number of records, where:

• state = NY
• where time (HOUR) is the same

To obtain:

• 2x 09
• 1x 11
• 1x 14

And the same for LA in another request.

Is it possible to link queries to 7 queries instead of 77 using group byor join?

I don't need any information to display, just the number of lines.

Answer 1

You can simply use the group by state and time.

select group_concat(count(date_trunc('hour',time))," X ",date_trunc('hour',time)),state 
from table group by state, date_trunc('hour',time)

Answer 2

!

SELECT
state
, time_hour
, count(*)
FROM
(
SELECT
state 
, SUBSTR(time,12,2) AS time_hour
FROM your_table
) generate_time_hour
GROUP BY state, time_hour

Answer 3

, , :

without concatenation

SELECT COUNT(*), HOUR(time) AS time, state
FROM `example`
GROUP BY state, HOUR(time)

with concatenation

SELECT CONCAT(COUNT(*), "x", TIME_FORMAT(time, "%H")) AS cnt, state
FROM `example`
GROUP BY state, HOUR(time)