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 !!!