sql query to combine two records with same id

I have created a query to calculate PO Quantity and SO Quantity. The problem is that for SO quantity i get two rows for same product with different SO quantity. What i want to do is add the SO quantity for the same products and display it as a single row in database.Please advise.

Following is the query i have written:

SELECT DISTINCT products.ProductCode,
                products.ProductName,
                pe.stockreorderqty,
                pe.stocklowqtyalarm,
                products.StockStatus,
                pe.lastpo_qty,
                pe.lastpo_date,
                pe.vendor_price,
                Sum(PO_Items.POI_Quantity) - Sum(PO_Items.POI_QtyReceived) AS OutstandingPOQty,
                orderdetails.Quantity as outstandingsoqty
FROM Products INNER JOIN Products_Extended pe WITH (NOLOCK) ON products.ProductID = pe.ProductID 
LEFT JOIN PO_Items ON products.ProductCode = PO_Items.POI_ProductCode
LEFT JOIN orderdetails 
INNER JOIN orders ON orderdetails.OrderID = orders.OrderID  ON orderdetails.productcode = products.productcode 
    AND orders.OrderStatus <>'Cancelled' 
    AND orders.OrderStatus <>'Shipped'
    AND orders.OrderStatus <>'Returned'
GROUP BY products.ProductCode, products.ProductName, pe.开发者_开发知识库stockreorderqty,
         pe.stocklowqtyalarm, products.StockStatus, pe.lastpo_qty,
         pe.lastpo_date, pe.vendor_price, orderdetails.Quantity

The output i get for the query is following:

productcode
productname
stockreorderqty
stocklowqtyalarm
stockstatus
lastpo_qty
lastpo_date
vendor_price
outstandingpoqty
outstandingsoqty
10195
Carclo 19° Frosted 20mm Lens - No Holder
0
5
120
300
06/16/11 09:32 PM
1.05
0
50

Carclo 19° Frosted 20mm Lens - No Holder
0
5
120
300
06/16/11 09:32 PM
1.05
0
56

in the above output the product codes are same only the outstandingsoqty is different. I want to add the outstandingsoqty and display two rows as one single row. they have different outstandingsoqty for same product code as it is for different open sales order.

You can check the image for output at the following link: http://qedlr.fhmto.servertrust.com/v/vspfiles/assets/images/queryresult.png

There is simple way to to this. Don't use Group by where you are expecting large result set, it will make your query slow, Instead use sub-query.

Do this way:

SELECT DISTINCT [OTHER Fields], 
(SELECT Sum(PO_Items.POI_Quantity) - Sum(PO_Items.POI_QtyReceived) FROM PO_Items WHERE PO_Items.POI_ProductCode = products.ProductCode) as OutstandingPOQty,
(SELECT Sum(det1.Quantity) FROM orderdetails AS det1 WHERE det1.productcode =products.productcode and det1.OrderID=orders.OrderID) as outstandingsoqty
FROM Products 
INNER JOIN Products_Extended pe WITH (NOLOCK) ON products.ProductID = pe.ProductID 
LEFT JOIN orderdetails INNER JOIN orders ON orderdetails.OrderID = orders.OrderID ON orderdetails.productcode = products.productcode 
and orders.OrderStatus <>'Cancelled' and orders.OrderStatus <>'Shipped' and orders.OrderStatus <>'Returned' 

It will be pretty fast. Hope it helps.