title: How to subtract inventory and sale using mysql subquery?
body: <p>Trying to learn more on sub-query. I am looking for a way to subtract and compare two tables.</p>
  
  <ol>
  <li>Inventory</li>
  <li>Sales</li>
  </ol>
  
  <p>My data records are as follows:</p>
  
  <p><strong>Inventory:</strong></p>
  
  <pre><code>mysql&gt; select store_id, product_id, sum(quantity) as inventory from inventories where store_id = 1 group by product_id;
  +----------+------------+-----------+
  | store_id | product_id | inventory |
  +----------+------------+-----------+
  |        1 |          8 |        24 |
  |        1 |         10 |         4 |
  |        1 |         14 |        24 |
  +----------+------------+-----------+
  3 rows in set (0.00 sec)
  </code></pre>
  
  <p><strong>Sales</strong></p>
  
  <pre><code>mysql&gt; select store_id, product_id, sum(quantity) as sales from sales where store_id = 1 group by product_id;
  +----------+------------+-------+
  | store_id | product_id | sales |
  +----------+------------+-------+
  |        1 |          8 |    12 |
  |        1 |         14 |     2 |
  |        1 |          8 |     1 |
  +----------+------------+-------+
  2 rows in set (0.00 sec)
  </code></pre>
  
  <p>What is the proper sub-query to have the following result?</p>
  
  <pre><code>+----------+------------+-----------+-------+-----------+
  | store_id | product_id | inventory | sales | remaining |
  +----------+------------+-----------+-------+-----------+
  |        1 |          8 |        24 |    12 |        12 |
  |        1 |         14 |        24 |     2 |        22 |
  |        1 |          8 |        12 |     1 |        11 |
  +----------+------------+-----------+-------+-----------+
  </code></pre>