SUM values from two tables with GROUP BY and WHERE












0















I have two tables below named sent_table and received_table. I am attempting to mash them together in a query to achieve output_table. All my attempts so far result in a huge amount of duplicates and totally bogus sum values.



I am assuming I would need to use GROUP BY and WHERE to achieve this goal. I want to be able to filter based on the users name.



sent_table



+----+------+-------+----------+
| id | name | value | order_id |
+----+------+-------+----------+
| 1 | dave | 100 | 1 |
| 2 | dave | 200 | 1 |
| 3 | dave | 300 | 2 |
+----+------+-------+----------+


received_table



+----+------+-------+----------+
| id | name | value | order_id |
+----+------+-------+----------+
| 1 | dave | 400 | 1 |
| 2 | dave | 500 | 2 |
| 3 | dave | 600 | 2 |
+----+------+-------+----------+


output table



+------+----------+----------+
| sent | received | order_id |
+------+----------+----------+
| 300 | 400 | 1 |
| 300 | 1100 | 2 |
+------+----------+----------+


I tried the following with no joy. This does not impose any restrictions on how I would desire to solve this problem. It is just how I attempted to do it.



SELECT *
FROM
( select SUM(value) as sent, order_id FROM sent_table WHERE name='dave' GROUP BY order_id) A
CROSS JOIN
( select SUM(value) as received, order_id FROM received_table WHERE name='dave' GROUP BY order_id) B


Any help would be greatly appreciated.










share|improve this question



























    0















    I have two tables below named sent_table and received_table. I am attempting to mash them together in a query to achieve output_table. All my attempts so far result in a huge amount of duplicates and totally bogus sum values.



    I am assuming I would need to use GROUP BY and WHERE to achieve this goal. I want to be able to filter based on the users name.



    sent_table



    +----+------+-------+----------+
    | id | name | value | order_id |
    +----+------+-------+----------+
    | 1 | dave | 100 | 1 |
    | 2 | dave | 200 | 1 |
    | 3 | dave | 300 | 2 |
    +----+------+-------+----------+


    received_table



    +----+------+-------+----------+
    | id | name | value | order_id |
    +----+------+-------+----------+
    | 1 | dave | 400 | 1 |
    | 2 | dave | 500 | 2 |
    | 3 | dave | 600 | 2 |
    +----+------+-------+----------+


    output table



    +------+----------+----------+
    | sent | received | order_id |
    +------+----------+----------+
    | 300 | 400 | 1 |
    | 300 | 1100 | 2 |
    +------+----------+----------+


    I tried the following with no joy. This does not impose any restrictions on how I would desire to solve this problem. It is just how I attempted to do it.



    SELECT *
    FROM
    ( select SUM(value) as sent, order_id FROM sent_table WHERE name='dave' GROUP BY order_id) A
    CROSS JOIN
    ( select SUM(value) as received, order_id FROM received_table WHERE name='dave' GROUP BY order_id) B


    Any help would be greatly appreciated.










    share|improve this question

























      0












      0








      0








      I have two tables below named sent_table and received_table. I am attempting to mash them together in a query to achieve output_table. All my attempts so far result in a huge amount of duplicates and totally bogus sum values.



      I am assuming I would need to use GROUP BY and WHERE to achieve this goal. I want to be able to filter based on the users name.



      sent_table



      +----+------+-------+----------+
      | id | name | value | order_id |
      +----+------+-------+----------+
      | 1 | dave | 100 | 1 |
      | 2 | dave | 200 | 1 |
      | 3 | dave | 300 | 2 |
      +----+------+-------+----------+


      received_table



      +----+------+-------+----------+
      | id | name | value | order_id |
      +----+------+-------+----------+
      | 1 | dave | 400 | 1 |
      | 2 | dave | 500 | 2 |
      | 3 | dave | 600 | 2 |
      +----+------+-------+----------+


      output table



      +------+----------+----------+
      | sent | received | order_id |
      +------+----------+----------+
      | 300 | 400 | 1 |
      | 300 | 1100 | 2 |
      +------+----------+----------+


      I tried the following with no joy. This does not impose any restrictions on how I would desire to solve this problem. It is just how I attempted to do it.



      SELECT *
      FROM
      ( select SUM(value) as sent, order_id FROM sent_table WHERE name='dave' GROUP BY order_id) A
      CROSS JOIN
      ( select SUM(value) as received, order_id FROM received_table WHERE name='dave' GROUP BY order_id) B


      Any help would be greatly appreciated.










      share|improve this question














      I have two tables below named sent_table and received_table. I am attempting to mash them together in a query to achieve output_table. All my attempts so far result in a huge amount of duplicates and totally bogus sum values.



      I am assuming I would need to use GROUP BY and WHERE to achieve this goal. I want to be able to filter based on the users name.



      sent_table



      +----+------+-------+----------+
      | id | name | value | order_id |
      +----+------+-------+----------+
      | 1 | dave | 100 | 1 |
      | 2 | dave | 200 | 1 |
      | 3 | dave | 300 | 2 |
      +----+------+-------+----------+


      received_table



      +----+------+-------+----------+
      | id | name | value | order_id |
      +----+------+-------+----------+
      | 1 | dave | 400 | 1 |
      | 2 | dave | 500 | 2 |
      | 3 | dave | 600 | 2 |
      +----+------+-------+----------+


      output table



      +------+----------+----------+
      | sent | received | order_id |
      +------+----------+----------+
      | 300 | 400 | 1 |
      | 300 | 1100 | 2 |
      +------+----------+----------+


      I tried the following with no joy. This does not impose any restrictions on how I would desire to solve this problem. It is just how I attempted to do it.



      SELECT *
      FROM
      ( select SUM(value) as sent, order_id FROM sent_table WHERE name='dave' GROUP BY order_id) A
      CROSS JOIN
      ( select SUM(value) as received, order_id FROM received_table WHERE name='dave' GROUP BY order_id) B


      Any help would be greatly appreciated.







      postgresql






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 25 '18 at 9:38









      DanDan

      10812




      10812
























          1 Answer
          1






          active

          oldest

          votes


















          1














          Do the sums on each table, grouping by order_id, then join the results. To get the rows even if one side is missing, do a FULL OUTER JOIN:



          SELECT COALESCE(s.order_id, r.order_id) AS order_id, s.sent, r.received
          FROM (
          SELECT order_id, SUM(value) AS sent
          FROM sent
          GROUP BY order_id
          ) s
          FULL OUTER JOIN (
          SELECT order_id, SUM(value) AS received
          FROM received
          GROUP BY order_id
          ) r
          USING (order_id)
          ORDER BY 1


          Result:



          | order_id | sent | received |
          | -------- | ---- | -------- |
          | 1 | 300 | 400 |
          | 2 | | 1100 |


          Note the COALESCE on the order_id, so that if it's missing from sent it will be taken from recevied, so that that value will never be NULL.



          If you want to have 0 in place of NULL (when e.g. there is no record for that order_id in either sent or received), you would do COALESCE(s.sent, 0) AS sent, COALESCE(r.received, 0) AS received.



          https://www.db-fiddle.com/f/nq3xYrcys16eUrBRHT6xLL/2






          share|improve this answer


























          • That is awesome! :) One question, if sent does not have an order_id 2, the received data for order_id 2 does not appear. Is there any quick way to still get the order_id 2 data?

            – Dan
            Nov 25 '18 at 9:53













          • @Dan Updated example for that scenario in answer.

            – 404
            Nov 25 '18 at 10:03













          • You my friend are amazing. Thank you sooooo much. Works a treat. I will spend some time learning what is exactly going on here and develop my own knowledge now that I know this stuff is possible. Thanks again!

            – Dan
            Nov 25 '18 at 10:07











          Your Answer






          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "1"
          };
          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: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          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%2fstackoverflow.com%2fquestions%2f53466247%2fsum-values-from-two-tables-with-group-by-and-where%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          1














          Do the sums on each table, grouping by order_id, then join the results. To get the rows even if one side is missing, do a FULL OUTER JOIN:



          SELECT COALESCE(s.order_id, r.order_id) AS order_id, s.sent, r.received
          FROM (
          SELECT order_id, SUM(value) AS sent
          FROM sent
          GROUP BY order_id
          ) s
          FULL OUTER JOIN (
          SELECT order_id, SUM(value) AS received
          FROM received
          GROUP BY order_id
          ) r
          USING (order_id)
          ORDER BY 1


          Result:



          | order_id | sent | received |
          | -------- | ---- | -------- |
          | 1 | 300 | 400 |
          | 2 | | 1100 |


          Note the COALESCE on the order_id, so that if it's missing from sent it will be taken from recevied, so that that value will never be NULL.



          If you want to have 0 in place of NULL (when e.g. there is no record for that order_id in either sent or received), you would do COALESCE(s.sent, 0) AS sent, COALESCE(r.received, 0) AS received.



          https://www.db-fiddle.com/f/nq3xYrcys16eUrBRHT6xLL/2






          share|improve this answer


























          • That is awesome! :) One question, if sent does not have an order_id 2, the received data for order_id 2 does not appear. Is there any quick way to still get the order_id 2 data?

            – Dan
            Nov 25 '18 at 9:53













          • @Dan Updated example for that scenario in answer.

            – 404
            Nov 25 '18 at 10:03













          • You my friend are amazing. Thank you sooooo much. Works a treat. I will spend some time learning what is exactly going on here and develop my own knowledge now that I know this stuff is possible. Thanks again!

            – Dan
            Nov 25 '18 at 10:07
















          1














          Do the sums on each table, grouping by order_id, then join the results. To get the rows even if one side is missing, do a FULL OUTER JOIN:



          SELECT COALESCE(s.order_id, r.order_id) AS order_id, s.sent, r.received
          FROM (
          SELECT order_id, SUM(value) AS sent
          FROM sent
          GROUP BY order_id
          ) s
          FULL OUTER JOIN (
          SELECT order_id, SUM(value) AS received
          FROM received
          GROUP BY order_id
          ) r
          USING (order_id)
          ORDER BY 1


          Result:



          | order_id | sent | received |
          | -------- | ---- | -------- |
          | 1 | 300 | 400 |
          | 2 | | 1100 |


          Note the COALESCE on the order_id, so that if it's missing from sent it will be taken from recevied, so that that value will never be NULL.



          If you want to have 0 in place of NULL (when e.g. there is no record for that order_id in either sent or received), you would do COALESCE(s.sent, 0) AS sent, COALESCE(r.received, 0) AS received.



          https://www.db-fiddle.com/f/nq3xYrcys16eUrBRHT6xLL/2






          share|improve this answer


























          • That is awesome! :) One question, if sent does not have an order_id 2, the received data for order_id 2 does not appear. Is there any quick way to still get the order_id 2 data?

            – Dan
            Nov 25 '18 at 9:53













          • @Dan Updated example for that scenario in answer.

            – 404
            Nov 25 '18 at 10:03













          • You my friend are amazing. Thank you sooooo much. Works a treat. I will spend some time learning what is exactly going on here and develop my own knowledge now that I know this stuff is possible. Thanks again!

            – Dan
            Nov 25 '18 at 10:07














          1












          1








          1







          Do the sums on each table, grouping by order_id, then join the results. To get the rows even if one side is missing, do a FULL OUTER JOIN:



          SELECT COALESCE(s.order_id, r.order_id) AS order_id, s.sent, r.received
          FROM (
          SELECT order_id, SUM(value) AS sent
          FROM sent
          GROUP BY order_id
          ) s
          FULL OUTER JOIN (
          SELECT order_id, SUM(value) AS received
          FROM received
          GROUP BY order_id
          ) r
          USING (order_id)
          ORDER BY 1


          Result:



          | order_id | sent | received |
          | -------- | ---- | -------- |
          | 1 | 300 | 400 |
          | 2 | | 1100 |


          Note the COALESCE on the order_id, so that if it's missing from sent it will be taken from recevied, so that that value will never be NULL.



          If you want to have 0 in place of NULL (when e.g. there is no record for that order_id in either sent or received), you would do COALESCE(s.sent, 0) AS sent, COALESCE(r.received, 0) AS received.



          https://www.db-fiddle.com/f/nq3xYrcys16eUrBRHT6xLL/2






          share|improve this answer















          Do the sums on each table, grouping by order_id, then join the results. To get the rows even if one side is missing, do a FULL OUTER JOIN:



          SELECT COALESCE(s.order_id, r.order_id) AS order_id, s.sent, r.received
          FROM (
          SELECT order_id, SUM(value) AS sent
          FROM sent
          GROUP BY order_id
          ) s
          FULL OUTER JOIN (
          SELECT order_id, SUM(value) AS received
          FROM received
          GROUP BY order_id
          ) r
          USING (order_id)
          ORDER BY 1


          Result:



          | order_id | sent | received |
          | -------- | ---- | -------- |
          | 1 | 300 | 400 |
          | 2 | | 1100 |


          Note the COALESCE on the order_id, so that if it's missing from sent it will be taken from recevied, so that that value will never be NULL.



          If you want to have 0 in place of NULL (when e.g. there is no record for that order_id in either sent or received), you would do COALESCE(s.sent, 0) AS sent, COALESCE(r.received, 0) AS received.



          https://www.db-fiddle.com/f/nq3xYrcys16eUrBRHT6xLL/2







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Nov 25 '18 at 10:07

























          answered Nov 25 '18 at 9:46









          404404

          3,0851726




          3,0851726













          • That is awesome! :) One question, if sent does not have an order_id 2, the received data for order_id 2 does not appear. Is there any quick way to still get the order_id 2 data?

            – Dan
            Nov 25 '18 at 9:53













          • @Dan Updated example for that scenario in answer.

            – 404
            Nov 25 '18 at 10:03













          • You my friend are amazing. Thank you sooooo much. Works a treat. I will spend some time learning what is exactly going on here and develop my own knowledge now that I know this stuff is possible. Thanks again!

            – Dan
            Nov 25 '18 at 10:07



















          • That is awesome! :) One question, if sent does not have an order_id 2, the received data for order_id 2 does not appear. Is there any quick way to still get the order_id 2 data?

            – Dan
            Nov 25 '18 at 9:53













          • @Dan Updated example for that scenario in answer.

            – 404
            Nov 25 '18 at 10:03













          • You my friend are amazing. Thank you sooooo much. Works a treat. I will spend some time learning what is exactly going on here and develop my own knowledge now that I know this stuff is possible. Thanks again!

            – Dan
            Nov 25 '18 at 10:07

















          That is awesome! :) One question, if sent does not have an order_id 2, the received data for order_id 2 does not appear. Is there any quick way to still get the order_id 2 data?

          – Dan
          Nov 25 '18 at 9:53







          That is awesome! :) One question, if sent does not have an order_id 2, the received data for order_id 2 does not appear. Is there any quick way to still get the order_id 2 data?

          – Dan
          Nov 25 '18 at 9:53















          @Dan Updated example for that scenario in answer.

          – 404
          Nov 25 '18 at 10:03







          @Dan Updated example for that scenario in answer.

          – 404
          Nov 25 '18 at 10:03















          You my friend are amazing. Thank you sooooo much. Works a treat. I will spend some time learning what is exactly going on here and develop my own knowledge now that I know this stuff is possible. Thanks again!

          – Dan
          Nov 25 '18 at 10:07





          You my friend are amazing. Thank you sooooo much. Works a treat. I will spend some time learning what is exactly going on here and develop my own knowledge now that I know this stuff is possible. Thanks again!

          – Dan
          Nov 25 '18 at 10:07


















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Stack Overflow!


          • 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%2fstackoverflow.com%2fquestions%2f53466247%2fsum-values-from-two-tables-with-group-by-and-where%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

          A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

          Calculate evaluation metrics using cross_val_predict sklearn

          Insert data from modal to MySQL (multiple modal on website)