Vryjdfkk

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?