SQL Server query related to grouping and counting for inventory analysis

Question

My table is concerned with inventory items, stocking levels and required numbers. An example is given below:

Part     Sub Part         Pieces Required        Pieces In Stock

Barbie    Legs                 2                      1000
Barbie    Arms                 2                      5000
Barbie    Head                 1                       20
Barbie    Torso                1                     40000
Dora      Legs                 2                      1000
Dora      Arms                 2                      5000
Dora      Head                 1                       0
Dora     Torso                 1                     40000

I want my end result to look like:

Part         No: of dolls that can be built

Barbie           20
Dora              0

So the logic is we need a minimum number of each part to make a complete doll. If even one of the required parts is not in stock, then no doll can be made. The complexity comes when we need 2 of certain parts and only 1 of other parts. How do I achieve this using SQL Server?

Thank you in advance for your help and time.

Answer 1

SELECT part, MIN([Pieces In Stock]/[Pieces Required]) AS PossibleCompleteDolls
FROM tblName 
WHERE [Pieces Required] <> 0
GROUP BY part;

The WHERE clause here is just to prevent division by zero.