How can one fetch subtotal in relation to a group by from an outer query?
In order to get the missing data, first you need to create a table with all the hours.  Then LEFT JOIN from that table to your table.

You may also need a COALESCE(..., 0) to turn NULL into 0.