SELECT
    ItemPrice.ItemID,
    COALESCE( ItemPrice.MetalPrice, 0 ) + COALESCE(
    (SELECT SUM( PartsPrice.PartPriceTotal )
     FROM PartsPrice
     WHERE ItemPrice.ItemID = PartsPrice.ItemID
     GROUP BY PartsPrice.ItemID), 0) AS FinalItemPrice,
    ItemPrice.MetalPrice,
    (SELECT SUM(PartsPrice.PartPriceTotal)
     FROM PartsPrice
     WHERE ItemPrice.ItemID = PartsPrice.ItemID
     GROUP BY PartsPrice.ItemID) AS PartTotalPrice
FROM
    ItemPrice
WHERE
    ItemPrice.ItemID = '100456'
GROUP BY
    ItemPrice.MetalPrice,
    ItemPrice.ItemID

SELECT
      itp.ItemID
    , COALESCE(itp.MetalPrice, 0) + COALESCE( SUM(pp.PartPriceTotal) , 0) AS FinalItemPrice
    , itp.MetalPrice
    , SUM(pp.PartPriceTotal)    AS PartTotalPrice
FROM ItemPrice itp
LEFT JOIN PartsPrice pp ON itp.ItemID = pp.ItemID
WHERE itp.ItemID = '100456'
GROUP BY itp.MetalPrice, itp.ItemID

SELECT I.ItemID,
       COALESCE(I.MetalPrice, 0) + PT.Total AS FinalItemPrice,
       I.MetalPrice,
       PT.Total AS PartTotalPrice
FROM ItemPrice I
     CROSS APPLY (SELECT SUM(PP.PartPriceTotal) AS Total
                  FROM PartsPrice  PP
                  WHERE I.ItemID = PP.ItemID
                  GROUP BY PP.ItemID) PT
WHERE I.ItemID = '100456'
GROUP BY I.MetalPrice,
         I.ItemID;

SELECT I.ItemID,
       COALESCE(I.MetalPrice, 0) + SUM(PP.PartPriceTotal) AS FinalItemPrice,
       I.MetalPrice,
       SUM(PP.PartPriceTotal) AS PartTotalPrice
FROM ItemPrice I
     JOIN PartsPrice PP ON I.ItemID = PP.ItemID
WHERE I.ItemID = '100456'
GROUP BY I.MetalPrice,
         I.ItemID;

SELECT
    ItemPrice.ItemID,
    COALESCE( ItemPrice.MetalPrice, 0 ) + COALESCE( sq.PartPriceTotal, 0 ) AS FinalItemPrice,
    ItemPrice.MetalPrice,
    COALESCE( sq.PartPriceTotal, 0 ) AS PartTotalPrice
FROM
    ItemPrice
    LEFT OUTER JOIN
    (
        SELECT
            PartsPrice.ItemID,
            SUM( PartsPrice.PartPriceTotal )
        FROM
            PartsPrice
        GROUP BY
            PartsPrice.ItemID
    ) AS sq ON ItemPrice.ItemID = sq.ItemID
WHERE
    ItemPrice.ItemID = '100456'
GROUP BY
    ItemPrice.MetalPrice,
    ItemPrice.ItemID

SELECT
    ItemPrice.ItemID,
    COALESCE (ItemPrice.MetalPrice, 0) + COALESCE (t2.partpricetotal, 0) AS FinalItemPrice,
    ItemPrice.MetalPrice,
    t2.PartTotalPrice
FROM ItemPrice
    LEFT OUTER JOIN (SELECT ItemID, SUM(PartsPrice.PartPriceTotal) as PartPriceTotal FROM PartsPrice GROUP BY PartsPrice.ItemID) t2
        ON ItemPrice.ItemID = PartsPrice.ItemID
WHERE ItemPrice.ItemID = '100456'