Use of Sum function

Hi All, I wrote the following Query

SELECT Item.Description,Item.[No_],POC.[Qty on Component Line] FROM [Company Name$Item] AS Item LEFT OUTER JOIN (SELECT [Item No_],Status,Sum([Remaining Quantity]) AS [Qty on Component Line] FROM [Company Name$Prod_ Order Component] Group By Status, [Item No_]) AS POC ON Item. [No_] = POC.[Item No_] AND ((POC.Status = 1) OR (POC.Status = 2) OR (POC.Status = 3)) WHERE (Item.[Buy on Hold] = 1) AND Item.[No_] = '01-12008'

With this query what i want to acheive is that for the item '01-12008' i should have only one record as a result.but what i am getting is 2 records because the POC table has 4 lines, 3 for status = 2 and 1 for status = 1. I am getting the sum of Remaining Qty from POC table for status = 2.
What i want is that it should give only 1 record as result by summing up the quantity for POC table if status is 1 or 2 or 3.

Move the WHERE clause for Status to your derived table (alias POC).
Also remove Status from the column list and the GROUP BY clause of the derived table.

Hi, Here is the full code.

SELECT Item.No_, Item.Description, ILE.Inventory, SO.[Qty on Sales Order], PO.[Qty on Purch Order], Customer.Name, Customer.[Salesperson Code],
Customer.[Buyer Code], Item.[Buy on Hold], CL.[Last of USER ID], CL.[Primary Key Field 1 Value], CL.MaxOfDateTime, CommentLine.Comment,
POC.[Qty on Component Lines]
FROM [Company$Item] AS Item LEFT OUTER JOIN
(SELECT [Item No_], ISNULL(SUM(Quantity), 0) AS Inventory
FROM [Company$Item Ledger Entry]
GROUP BY [Item No_]) AS ILE ON Item.No_ = ILE.[Item No_] LEFT OUTER JOIN
(SELECT No_, [Document Type], Type, ISNULL(SUM([Outstanding Quantity]), 0) AS [Qty on Sales Order]
FROM [Company$Sales Line]
GROUP BY No_, [Document Type], Type) AS SO ON Item.No_ = SO.No_ AND SO.[Document Type] = 1 AND SO.Type = 2 LEFT OUTER JOIN
(SELECT No_, [Document Type], Type, ISNULL(SUM([Outstanding Quantity]), 0) AS [Qty on Purch Order]
FROM [Company$Purchase Line]
GROUP BY No_, [Document Type], Type) AS PO ON Item.No_ = PO.No_ AND PO.[Document Type] = 1 AND PO.Type = 2 LEFT OUTER JOIN
(SELECT No_, Name, [Salesperson Code], [Buyer Code]
FROM [Company$Customer]) AS Customer ON Item.[Customer No_] = Customer.No_ LEFT OUTER JOIN
(SELECT MAX([User ID]) AS [Last of USER ID], [Table No_], [Field No_], [Old Value], [New Value], [Primary Key Field 1 Value], MAX([Date and Time])
AS MaxOfDateTime
FROM [Company$Change Log Entry]
GROUP BY [Table No_], [Field No_], [Old Value], [New Value], [Primary Key Field 1 Value]) AS CL ON CL.[Table No_] = 27 AND
CL.[Field No_] = 50031 AND CL.[Old Value] = 'No' AND CL.[New Value] = 'Yes' AND Item.No_ = CL.[Primary Key Field 1 Value] LEFT OUTER JOIN
(SELECT Comment, Code, No_, [Table Name]
FROM [Company$Comment Line]) AS CommentLine ON Item.No_ = CommentLine.No_ AND CommentLine.Code = 'BOH' AND
CommentLine.[Table Name] = 3 LEFT OUTER JOIN
(SELECT Status, [Item No_], SUM([Remaining Quantity]) AS [Qty on Component Lines]
FROM [Company$Prod_ Order Component]
GROUP BY [Item No_], Status) AS POC ON POC.Status < 4 AND Item.No_ = POC.[Item No_]
WHERE (Item.[Buy on Hold] = 1)
ORDER BY CL.MaxOfDateTime, Customer.[Salesperson Code], Customer.Name

For every thing like ILE, PurchLine etc the sum is coming correct but for POC it is summing up Status wise like for status = 2 there are 5 lines for an item in POC table and for status = 3 it has 4 lines. So it is summing 'Remaining Quantity' column first for status = 2 and thn again for status = 4. so i m getting 2 records instaed of one. What i want that it should some of all the remaining Quantity for status < 4.