title: something wrong with the result of mysql query with joins and select
body: <p>Good day,</p>
  
  <p>I am trying to join 3 tables for my inventory report but I am getting weird results out of it. </p>
  
  <p>my query</p>
  
  <pre><code>SELECT i_inventory.xid,
         count(x_transaction_details.xitem) AS occurrence,
         i_inventory.xitem AS itemName,
         SUM(i_items_group.or_qty) AS `openingQty`,
         avg(x_transaction_details.cost) AS avg_cost,
         SUM(x_transaction_details.qty) AS totalNumberSold,
         SUM(i_items_group.or_qty) - SUM(x_transaction_details.qty) AS totalRemQty
  FROM x_transaction_details
  LEFT JOIN i_inventory ON x_transaction_details.xitem = i_inventory.xid
  LEFT JOIN i_items_group ON i_inventory.xid = i_items_group.xitem
  WHERE (x_transaction_details.date_at BETWEEN '2015-01-18 03:14:54' AND '2015-10-18 03:14:54')
    AND i_inventory.xid = 3840
  GROUP BY x_transaction_details.xitem
  ORDER BY occurrence DESC
  </code></pre>
  
  <p>This query gives me this result:
  <a href="https://i.stack.imgur.com/Tl0OO.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Tl0OO.png" alt="enter image description here"></a></p>
  
  <p>See the <code>openingQty</code> column, I then tried to do a simple query to verify the result,</p>
  
  <p>here's my query for checking the openingQty with joining only 2 tables <code>i_items_group</code> table (batches are stored) and <code>i_inventory</code> table (item Information are stored).</p>
  
  <pre><code>SELECT i_inventory.xid,
         i_inventory.xitem,
         SUM(i_items_group.or_qty) AS openingQty,
         i_items_group.cost
  FROM i_inventory
  INNER JOIN i_items_group ON i_inventory.xid = i_items_group.xitem
  WHERE i_inventory.xid = 3840
    AND (i_items_group.date_at BETWEEN '2015-01-18 03:14:54' AND '2015-10-18 03:14:54')
  </code></pre>
  
  <p>my result was:</p>
  
  <p><a href="https://i.stack.imgur.com/0OIsd.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0OIsd.png" alt="enter image description here"></a></p>
  
  <p>which is the correct data.</p>
  
  <p>I also made a query on my <code>x_transaction_details</code> table also to verify if its correct or not.</p>
  
  <p>heres my query:</p>
  
  <pre><code>select xitem, qty as qtySold from x_transaction_details where xitem = 3840 
  AND (date_at BETWEEN '2015-01-18 03:14:54' AND '2015-10-18 03:14:54')
  </code></pre>
  
  <p>result:</p>
  
  <p><a href="https://i.stack.imgur.com/cKFYJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cKFYJ.png" alt="enter image description here"></a></p>
  
  <p>Which would total to: 15-quatitySold.</p>
  
  <p>I'm just confused on how did I get 3269 as a result of my query where as the true openingQty should be only 467. </p>
  
  <p>I guess the problem was in my query with joins, its messing up with number of transactions then it sums it up (I really dont know though). </p>
  
  <p>Can you please help me identify it, and help me come up with the correct query.</p>