Post reply

The sum of items in a column based off of a Part Number

  • November 1, 2018 at 10:47 am

    #378416

    Hey guys! I am trying to rebuild an old inventory report and have ran into a bit of an issue. As stated in the title I am trying to find the total amount of each item. So far, I have gotten it to the point where it shows accurate results, but the goal is to have only one instance of each item in the result set along with a total or "System_Count". These are the results that I have gotten so far. The results circled in red should be one result with the sum, for example, with 2500005519 I want to see one result with a system count of 34. Thanks ahead of time to whoever can assist! 

    SELECT Part_LocationsReorderLevel.Bin, Part.PartNum, Part.Description, UnitOfMeasure.Code, WorkRequest_PartRequest.QuantityRequested - Inventory_Utilization.QuantityUtilized AS System_Count
    FROM Inventory_Utilization LEFT OUTER JOIN (WorkRequest_PartRequest LEFT OUTER JOIN 
    (Part_Locations LEFT OUTER JOIN ((UnitOfMeasure LEFT OUTER JOIN Part ON UnitOfMeasure.LegacyKey = Part.Part_UnitOfMeasureID) LEFT OUTER JOIN 
    Part_LocationsReorderLevel ON Part.ID = Part_LocationsReorderLevel.PartID) ON Part_Locations.ID = Part_LocationsReorderLevel.Part_LocationsID)
    ON WorkRequest_PartRequest.Part_PartID = Part.PartID) ON Inventory_Utilization.Part_PartID = Part.PartID
    WHERE Part_Locations.Description ='FILTER HOUSE'
    GROUP BY Part_LocationsReorderLevel.Bin, Part.PartNum, Part.Description, UnitOfMeasure.Code, WorkRequest_PartRequest.QuantityRequested - Inventory_Utilization.QuantityUtilized 
    Order By [Part_LocationsReorderLevel].[Bin];

  • November 1, 2018 at 10:55 am

    #2012077

    Replace this WorkRequest_PartRequest.QuantityRequested - Inventory_Utilization.QuantityUtilized
    with SUM(WorkRequest_PartRequest.QuantityRequested - Inventory_Utilization.QuantityUtilized)
    and then take the expression out of the GROUP BY clause also.

  • November 1, 2018 at 11:08 am

    #2012080

    mark.humphreys - Thursday, November 1, 2018 10:55 AM

    Replace this WorkRequest_PartRequest.QuantityRequested - Inventory_Utilization.QuantityUtilized
    with SUM(WorkRequest_PartRequest.QuantityRequested - Inventory_Utilization.QuantityUtilized)
    and then take the expression out of the GROUP BY clause also.

    You would think that this code would be correct (I tried that before posting here), but it gives you some absurdly large and incorrect numbers
    SELECT Part_LocationsReorderLevel.Bin, Part.PartNum, Part.Description, UnitOfMeasure.Code, SUM(WorkRequest_PartRequest.QuantityRequested - Inventory_Utilization.QuantityUtilized) AS System_Count
    FROM Inventory_Utilization LEFT OUTER JOIN (WorkRequest_PartRequest LEFT OUTER JOIN
    (Part_Locations LEFT OUTER JOIN ((UnitOfMeasure LEFT OUTER JOIN Part ON UnitOfMeasure.LegacyKey = Part.Part_UnitOfMeasureID) LEFT OUTER JOIN
    Part_LocationsReorderLevel ON Part.ID = Part_LocationsReorderLevel.PartID) ON Part_Locations.ID = Part_LocationsReorderLevel.Part_LocationsID)
    ON WorkRequest_PartRequest.Part_PartID = Part.PartID) ON Inventory_Utilization.Part_PartID = Part.PartID
    WHERE Part_Locations.Description ='FILTER HOUSE'
    GROUP BY Part_LocationsReorderLevel.Bin, Part.PartNum, Part.Description, UnitOfMeasure.Code
    Order By [Part_LocationsReorderLevel].[Bin];

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply