Mysql – Subquery in SELECT statement (MySQL)

mysqlselectsubquery

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.

The structure of my table (with some sample data) is as follows:

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  

I need a result similar to this one:

productID - size - stockBCN (BCN + BCN2) - stockMAD  
1         - S    - 2                     - 3  
2         - S    - 0                     - 2

I'm using this statement, and works fine just for an unique product

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'

How can I change it to make it work for all the rows in the table?

Best Solution

You probably want to leverage the GROUP BY clause instead of going with DISTINCT there, i.e.:

SELECT productId, Size,
SUM(CASE WHEN storeID IN ('BCN', 'BCN2') THEN stock ELSE 0 END) AS stockBCN,
SUM(CASE WHEN storeID = 'MAD' THEN stock ELSE 0 END) AS stockMAD
FROM stocks
GROUP BY productId, Size