Sentence in MySQL to count the records entered per hour

1

I have a table called records with the following columns:

I am trying to create a statement that allows me to obtain the total number of messages per hour and the total of classified by the status column and this grouped by the column start_time, being the field status with value 1, 2 and 3 and field start_time a data of type datetime. So far I have the following sentence:

SELECT 
    start_time,
    COUNT(status) as callsbyhour,
    (SELECT COUNT(status) FROM records HAVING status = 1 AND id = R.id ) AS  totalStatus1,
    (SELECT COUNT(*) FROM records WHERE status = 2 AND R.start_time = start_time ) AS totalStatus2,
    (SELECT COUNT(*) FROM records WHERE status >= 2 AND id = R.id) AS totalStatus3
FROM 
    records R
WHERE
    start_time >= '2018-07-02 06:00:00'
AND
    start_time < '2018-07-03 05:59:59'
GROUP BY
    HOUR(start_time)
ORDER BY 1 ASC

The total per hour gives it to me correctly, but the total by grouped status per hour does not show what was expected, doing a test throws me the following:

And this is incorrect since of the 11 total calls 7 are with status 1, 0 with status 2, and 3 with status 3.

I hope you can help me.

Thank you very much in advance.

Update 07-09-2018 18:07

Friends,

I found the solution I left the query in case someone came to serve.

SELECT 
start_time,
COUNT(status) as callsbyhour,
SUM((SELECT COUNT(status) FROM records WHERE status = 1 AND start_time = R.start_time)) AS  totalStatus1,
SUM((SELECT COUNT(status) FROM records WHERE status = 2 AND start_time = R.start_time )) AS totalStatus2,
SUM((SELECT COUNT(status) FROM records WHERE status >= 3 AND start_time =     R.start_time)) AS totalStatus3
FROM 
records R
WHERE
start_time >= '2018-07-02 06:00:00'
AND
start_time < '2018-07-03 05:59:59'
GROUP BY HOUR(start_time)
ORDER BY 1

I found part of the solution in this question:

how to get totals for groups in MySQL?

Thank you community !!!

    
asked by Efren Anastacio 07.09.2018 в 22:36
source

0 answers