1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH RECURSIVE cte AS (
    SELECT 0 AS num

    UNION ALL

    SELECT num + 1
    FROM cte
    WHERE num < 23
), ANIMAL_OUTS_REFACTOR AS (
    SELECT HOUR(DATETIME) AS HOUR
         , COUNT(ANIMAL_ID) AS CNTS
    FROM ANIMAL_OUTS
    GROUP BY HOUR
    ORDER BY HOUR
)

SELECT cte.num
     , IFNULL(O.CNTS, 0)
FROM cte
    LEFT JOIN ANIMAL_OUTS_REFACTOR AS O ON cte.num = O.HOUR