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",
        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?


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.