title: How to calculate inventory using mysql
body: <p>I am looking for a way to calculate stock quantity from two tables.</p>
  
  <p>My table records are as follows:</p>
  
  <p>Below is my product table</p>
  
  <p><a href="https://i.stack.imgur.com/OWC3g.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/OWC3g.png" alt="enter image description here"></a></p>
  
  <p>I have two table first is "stockinward" for purchase the stock and another is stockoutward for sale out the stock.</p>
  
  <p>Below is the screenshot for stockinward table</p>
  
  <p><a href="https://i.stack.imgur.com/4Beyr.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4Beyr.png" alt="enter image description here"></a></p>
  
  <p>and here is the screenshot for stockoutward</p>
  
  <p><a href="https://i.stack.imgur.com/C5ntL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/C5ntL.png" alt="enter image description here"></a></p>
  
  <p>Below is my query to calculate the stock</p>
  
  <pre><code>SELECT 
  p.Id,
  p.Name,
  p.UnitPrice,
  ((SELECT 
          IFNULL(SUM(Quantity), 0)
      FROM
          stockinward
      WHERE
          ProductId = p.Id) - (SELECT 
          IFNULL(SUM(Quantity), 0)
      FROM
          stockoutward
      WHERE
          ProductId = p.Id)) AS Quantity
  </code></pre>
  
  <p>FROM
      product p;</p>
  
  <p><a href="https://i.stack.imgur.com/Ty6OZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Ty6OZ.png" alt="enter image description here"></a></p>
  
  <p>But the issue is in above query, when i have more then 1000 products it takes more then 8 second, so is there any other way in which i get the same result in 1 or 2 seconds?</p>
  
  <p>Thanks in advance :)</p>