SUM in a column based on the value in SKU field of the current record-CodePudding

I am migrating a tool I am building from excel to SQL due to its size (file size and complexity is making it exceptionally slow). I have a list of SKUs in various DCs with a count of each. I would like to create a SUM based on TotalOnHand of each SKU at each site. Essentially "add up the values from other warehouses where the SKU equals the current record's SKU." I can then take this value as a denominator to get a percentage of total inventory is at each warehouse. I am learning SQL for the first time for this project.

Current query:

    SELECT  Material, Plant, Unrestricted   [Unrestricted VOI] as TotalOnHand
    FROM [ATP_Snapshot].[Tables].[INVENTORY_REPT]
    WHERE Unrestricted >0
    AND [Unrestricted VOI] >0
    AND (
    Plant = 'US23'
    OR Plant = 'US27'
    OR Plant = 'US46'
    OR Plant = 'US49'
    )
    ORDER BY Plant, Material

Function I am trying to create. I am stumped where it ends.

    CREATE FUNCTION Inventory.PercentOfTotal (@TotalOnHand integer)
    RETURNS decimal (4,3)
    AS
    BEGIN
    DECLARE @PercentTotal decimal (4,3);
    SET @PercentTotal = @TotalOnHand/(SELECT SUM(@TotalOnHand) WHERE

Also, I am getting an error indicator where I understand the name goes (i.e. Inventory.PercentOfTotal). This is what I am trying to name my function.

CodePudding user response：

Your function will also require two input paramaters (one for the total across all plants and one for the total per plant). Functions are also typically slow as they process "row-by-agonizing-row" (check out Jeff Moden's RBAR for further explanation) so I wouldn't really use it here but if you want to learn about them then you should create one and test it.

Instead of a function I'd use the OVER clause for summing across various partitions.

The code would look something like this:

  SELECT  
        distinct(Material)
        ,Plant
        ,sum(Unrestricted   [Unrestricted VOI]) OVER(Partition By Plant) as TotalOnHandPerPlant
        ,sum(Unrestricted   [Unrestricted VOI]) OVER(Partition By Plant)/sum(Unrestricted   [Unrestricted VOI]) OVER()*100 as PercentOnHandPerPlant
        ,sum(Unrestricted   [Unrestricted VOI]) OVER() as TotalOnHandAllPlants
    FROM [ATP_Snapshot].[Tables].[INVENTORY_REPT]
    WHERE 
        Unrestricted > 0
        AND [Unrestricted VOI] > 0
        AND Plant in ('US23','US27','US27','US46','US49')
    ORDER BY 
        Plant
        , Material

CodePudding user response：

It depends how you want to show your results.

If you want each Material and Plant combination on a separate row then you need a window function. Note the use of windowing over an aggregation, SUM(SUM()).

SELECT
  ir.Material,
  ir.Plant,
  SUM(ir.Unrestricted   ir.[Unrestricted VOI]) AS TotalOnHand,
  SUM(ir.Unrestricted   ir.[Unrestricted VOI]) * 1.0
    / SUM(SUM(ir.Unrestricted   ir.[Unrestricted VOI])) OVER (PARTITION BY ir.Material) AS PctOnHand
FROM Tables.INVENTORY_REPT ir
WHERE ir.Unrestricted > 0
  AND ir.[Unrestricted VOI] > 0
  AND ir.Plant IN ('US23', 'US27', 'US46', 'US49')
GROUP BY
  ir.Material,
  ir.Plant
ORDER BY
  ir.Material,
  ir.Plant;

Or if you want them all on the same row then you need conditional aggregation, aka a pivot. Note the use of CROSS APPLY (VALUES to prevent repetition of the calculation, you can also do that in the query above.

SELECT
  ir.Material,
  ir.Plant,

  SUM(CASE WHEN ir.Plant = 'US23' THEN v.Total END) AS TotalOnHand_US23,
  SUM(CASE WHEN ir.Plant = 'US23' THEN v.Total END) * 1.0 / SUM(v.Total) AS PctOnHand_US23,
  SUM(CASE WHEN ir.Plant = 'US27' THEN v.Total END) AS TotalOnHand_US27,
  SUM(CASE WHEN ir.Plant = 'US27' THEN v.Total END) * 1.0 / SUM(v.Total) AS PctOnHand_US27,
  SUM(CASE WHEN ir.Plant = 'US46' THEN v.Total END) AS TotalOnHand_US46,
  SUM(CASE WHEN ir.Plant = 'US46' THEN v.Total END) * 1.0 / SUM(v.Total) AS PctOnHand_US46,
  SUM(CASE WHEN ir.Plant = 'US49' THEN v.Total END) AS TotalOnHand_US49,
  SUM(CASE WHEN ir.Plant = 'US49' THEN v.Total END) * 1.0 / SUM(v.Total) AS PctOnHand_US49

FROM Tables.INVENTORY_REPT ir
CROSS APPLY (VALUES (
    ir.Unrestricted   ir.[Unrestricted VOI]
) ) v(Total)
WHERE ir.Unrestricted > 0
  AND ir.[Unrestricted VOI] > 0
  AND ir.Plant IN ('US23', 'US27', 'US46', 'US49')
GROUP BY
  ir.Material
ORDER BY
  ir.Material;