title: MySQL One-To-Many SUM of COUNTs
body: <p>After 2 days of searching and trying similar questions, it's got to the point where I need to ask the question!</p>
  
  <p>I have the following database structure (simplified)..</p>
  
  <pre><code>mt_product           | mt_sku                              | mt_price
  ==========           | ======                              | ========
  id | brand_id | mpn  | id | product_id | retailer_id | sku | id | sku_id | price | date
  </code></pre>
  
  <p>For instance...<br>
  * A can of Coca-Cola is ONE product. <br>
  * It can be sold in many different retailers, who will all have a SKU for it.<br>
  * This SKU will have a price, which can change day-by-day.</p>
  
  <p>I want to list the total number of prices for the product.<br>
  To list this I currently have the following query which nearly works...</p>
  
  <pre><code>SELECT
  p.id AS pid, p.title AS p_title, p.cat, p.mpn,
  b.id AS bid, b.name AS brand,
  (SELECT COUNT(s.id) FROM mt_sku AS s WHERE s.pid = p.id) AS num_sku,
  (SELECT COUNT(gbp.id) FROM mt_price AS gbp INNER JOIN mt_sku ON mt_sku.id = gbp.sid ) AS num_price
  FROM mt_product AS p
  INNER JOIN mt_brand b ON p.bid = b.id
  INNER JOIN mt_sku s ON p.id = s.pid
  </code></pre>
  
  <p>num_sku returns as expected, however when I introduce the second sub query for num_price (and I have revised this many times) I either get...<br>
  * no duplications of the pid but the num_price is the total number of prices to SKUs, not the amount of prices for this product_id (as query above)<br> <a href="http://mrflibble.xyz/img/qry_eg1.PNG" rel="nofollow">eg1_img</a>
  * the correct number of num_price, but instead of totalling up the total num_price, the pid is duplicated in the table (as query below) - therefore as the pid is duplicated, this does not give me the result I want. I added DISTINCT as it helped an earlier version of the query, it now makes no difference.  <a href="http://mrflibble.xyz/img/qry_eg3.PNG" rel="nofollow">eg2_img</a></p>
  
  <pre><code>SELECT
  DISTINCT(p.id) AS pid, p.title AS p_title, p.cat, p.mpn,
  b.id AS bid, b.name AS brand,
  (SELECT COUNT(s.id) FROM mt_sku AS s WHERE s.pid = p.id) AS num_sku,
  (SELECT COUNT(gbp.id) FROM mt_price AS gbp WHERE s.id = gbp.sid) AS num_price
  FROM mt_product AS p
  INNER JOIN mt_brand b ON p.bid = b.id
  INNER JOIN mt_sku s ON p.id = s.pid
  </code></pre>
  
  <p>I'm pretty sure the key to this is that<br>
  product can have multiple SKUs, of which a SKU has multiple price history.<br>
  <br>
  Any help or ideas of the schema would be superb.</p>