title: How to retrieve two aggregate columns from two different tables using joins?
body: <p>I want to retrieve sum of <code>Price Column</code> from <code>ITEMS TABLE</code> and join it with the <code>GOODS TABLE</code>. The problem is when I try to join these tables, the sum of quantity column <code>gQnty</code> in Goods Table gives me <code>84</code> instead of <code>14</code> for <code>DISH A</code>. Can anyone help me how to achieve this task where I can get exact quantity <code>gQnty</code> and price <code>iPrice</code> with a same query.</p>
  <p>TABLE GOODS</p>
  <pre><code>gID | gName | gQnty
  -------------------------
  1     DISH A   10
  2     DISH B   12
  3     DISH A   4
  </code></pre>
  <p>TABLE ITEMS:</p>
  <pre><code>iID | gID | iItem | iPrice
  --------------------------
  1      1    Sugar    200
  2      1    Milk     300
  3      1    Fruits   100
  
  4      2    Oil      200
  
  5      3    Sugar    100
  6      3    Milk     50
  7      3    Fruits   40
  </code></pre>
  <p>My Query:</p>
  <pre><code>select g.gName, sum(g.gQnty)[gQnty], sum(i.iPrice)[iPrice]
  from goods g join items i
  on g.gID = i.gID
  group by g.gName
  </code></pre>
  <p>Expected Result:</p>
  <pre><code>gName  | gQnty | iPrice
  ----------------------------
  DISH A |  14   |  790  
  DISH B |  12   |  100
  </code></pre>