How can one fetch subtotal in relation to a group by from an outer query?

Question

I have a simple query, where I would like to display for past 7 days, grouped by hour from a table apiRequest: The count of request having a transaction vs the count of those having no transaction.

I tried two different approaches but both failed. The first column displays fine, it is the second one that is problematic:

+--------------+-------------+--------------------+-----------------+
| Date         | Time        | Total Requests     | no trans.       |
+--------------+-------------+--------------------+-----------------+
| January 20th | 2:00-3:00   |                 44 |               0 |
| January 20th | 3:00-4:00   |                 32 |               0 |
| January 20th | 5:00-6:00   |                 51 |               0 |
| January 20th | 6:00-7:00   |                 18 |               0 |

First approach was with a subquery, that nearly worked but I could not get the WHERE accountDetails NOT LIKE "%Transactions\":[{%" to work.

Second query was with an inner join, but could not manage to get the group by to work as expected for second column.

--  Two column table that displays number of requests for past seven days, grouped by hour of the day
-- First try, with subquery - the second column does not show result


--  Col 1 - Shows count of requests with transactions, 
--  Col 2 - count of requests without transactions
--  for every hour of the day in past seven days
SELECT DATE_FORMAT(a.created, "%M %D") Date,
       CONCAT(HOUR(a.created), ':00-', HOUR(a.created)+1, ':00') Time,
       count(a.`RequestId`) as "Total Requests",
         (select count(a2.`RequestId`) FROM apiRequest a2 WHERE DATE_FORMAT(f.created,"%Y %M %D %H") = DATE_FORMAT(a2.created, "%M %D %H")) "IBV's no trans."
        FROM apiRequest a
   WHERE a.created >= CURDATE() - INTERVAL 7 day
     AND a.RequestId IS NOT NULL 
GROUP BY DATE_FORMAT(a.created, "%M %D %H");


-- Second try, with inner join - group by does not work
SELECT DATE_FORMAT(a.created, "%M %D"),
       CONCAT(HOUR(a.created), ':00-', HOUR(f.created)+1, ':00') AS Hours,
       count(a.`RequestId`) as "Total requests",
       f2.ibvsNoTrans
        FROM apiRequest a
        INNER JOIN (SELECT COUNT(RequestId) ApiNoTrans FROM ApiRequest WHERE accountDetails NOT LIKE "%Transactions\":[{%" GROUP BY date_format(apiRequest.created, "%Y %M")) f2
           WHERE a.created >= CURDATE() - INTERVAL 7 day
     AND a.RequestId IS NOT NULL 
GROUP BY YEAR(a.created), MONTH(a.created), DAY(a.created), HOUR(a.created), Hours;

I am quite certain I am close to the goal but missing a detail. I feel this should be simpler, what is the proper approach, and is there a more optimal manner to match on dates / hours than what I have?

Answer 1

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.