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












0















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?









share



























    0















    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?









    share

























      0












      0








      0








      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?









      share














      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?







      mysql mysql-5.5





      share












      share










      share



      share










      asked 9 mins ago









      stefgosselinstefgosselin

      1104




      1104






















          0






          active

          oldest

          votes











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "182"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228174%2fhow-can-one-fetch-subtotal-in-relation-to-a-group-by-from-an-outer-query%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          0






          active

          oldest

          votes








          0






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes
















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Database Administrators Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228174%2fhow-can-one-fetch-subtotal-in-relation-to-a-group-by-from-an-outer-query%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          Liste der Baudenkmale in Friedland (Mecklenburg)

          Single-Malt-Whisky

          Czorneboh