title: MYSQL Group in one table and multiply with numbers from another table
body: <p>I have two tables as below:
  goods_in:</p>
  <pre><code>in_id|pid|in_num|in_date
  1     1001 10    2020-06-28
  2     1002 20    2020-06-28
  3     1001 20    2020-05-25
  ......
  </code></pre>
  <p>stock:</p>
  <pre><code>stock_id|pid|num|price
   1      1001   10   5
   2      1002   15   6
   3      1003   20   7
  ...
  </code></pre>
  <p>The &quot;goods_in&quot; table stores the records that all goods come into warehouse, the pid in this table is the same as in table &quot;stock&quot; which is the product ref code. There will be multiple records for each product in &quot;goods_in&quot; table.
  The &quot;stock&quot; table stores all the SKU we are holding and the current stock level as well as the product cost.
  What I'm trying to do is:</p>
  <ol>
  <li>Group by pid AND date (Year+Month) from &quot;goods_in&quot; so I get sub-total number of goods booked-in in each month.</li>
  <li>Multiple sub-total with stock.price.</li>
  <li>Get total amount of above, something like SUMPRODUCT in excel.</li>
  <li>Exports to html table or excel.</li>
  </ol>
  <p>I've tried several answers from SO with GROUP BY/ROLLUP/JOIN, and apparently I haven't made it right as expected. I can make this simple if I just add a cost column to the &quot;goods_in&quot; table but that would make it untidy.</p>
  <p>I'm still on my learning curves with MYSQL, forgive me if this looks simple to you guys.</p>
  <p>Thanks.</p>
  <p>Ken</p>