title: Subquery in SELECT statement (MySQL)
body: <p>I'm creating an SQL statement that will return a list of products and the quantity of each one of them I can find in every store.</p>
  
  <p>The structure of my table (with some sample data) is as follows:</p>
  
  <pre>
  productID - size - color - stock - storeID  
  1         - S    - RED01 - 1     - BCN   
  1         - S    - RED01 - 3     - MAD   
  2         - S    - YEL02 - 0     - BCN   
  2         - S    - YEL02 - 2     - MAD   
  1         - S    - RED01 - 1     - BCN2  
  </pre>
  
  <p>I need a result similar to this one: </p>
  
  <pre>
  productID - size - stockBCN (BCN + BCN2) - stockMAD  
  1         - S    - 2                     - 3  
  2         - S    - 0                     - 2
  </pre>
  
  <p>I'm using this statement, and works fine just for an unique product</p>
  
  <pre><code>SELECT DISTINCT prodID, size,
      (SELECT SUM(stock) FROM stocks WHERE storeID IN ('BCN','BCN2') AND prodID = 1 AND size = 'S' AND color = 'RED01' GROUP BY prodID, size, color) AS stockBCN,
      (SELECT SUM(stock) FROM stocks WHERE storeID = 'MAD' AND prodID = 1 AND size = 'S' AND color = 'RED01' GROUP BY prodID, size, color) AS stockMAD,
  FROM stocks WHERE storeID IN ('BCN','BCN2','MAD')
  AND prodID = (1) AND size = 'S' AND color = 'RED01'
  </code></pre>
  
  <p>How can I change it to make it work for all the rows in the table?</p>