SQL Server Performance

Use of Sum function

Discussion in 'SQL Server 2005 General Developer Questions' started by alisag, Feb 8, 2010.

  1. alisag New Member

    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.
  2. Adriaan New Member

    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.
  3. alisag New Member

    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.

Share This Page