Subquery in SELECT statement (MySQL)

Question

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?

Kevin Stricker · Accepted Answer · 2012-02-15 18:25:27Z

8

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

answered Feb 15, 2012 at 18:25

Kevin Stricker

17.4k5 gold badges49 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kaii Over a year ago

this solution will perform much better

xQbert · Accepted Answer · 2012-02-15 18:26:55Z

1

IMO prime time to use a case statement

Select productID, Size, Color,
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 
Where storeID in ('BCN', 'BCN2','MAD')
GROUP BY productID, Size, Color

answered Feb 15, 2012 at 18:26

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Collectives™ on Stack Overflow

Subquery in SELECT statement (MySQL)

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related