I wonder if anyone can assist on this product percentage breakdown challenge that I am having.

I have the following code to insert data into a table.

DROP TABLE IF EXISTS #ProductBreakdown
CREATE TABLE #ProductBreakdown
(
  Product VARCHAR(20)
  , [TotalSales] decimal(20, 2)
  , Percentage decimal(20, 2)
)
;

INSERT INTO #ProductBreakdown VALUES('Product A', 596.42, 0)
INSERT INTO #ProductBreakdown VALUES('Product A', 127.55, 0)
INSERT INTO #ProductBreakdown VALUES('Product A', 736.83, 0)
INSERT INTO #ProductBreakdown VALUES('Product B', 379.51, 0)
INSERT INTO #ProductBreakdown VALUES('Product B', 205.95, 0)
INSERT INTO #ProductBreakdown VALUES('Product C', 905.99, 0)

This SELECT query returns the correct percentage results, ie

SELECT PB.Product, PB.TotalSales, 
  [TotalSales] / SUM([TotalSales]) OVER(PARTITION BY Product) * 100 AS PercentageSales
FROM #ProductBreakdown PB

I would now like to commit those percentages into the table with an UPDATE statement. I am using the following statement

UPDATE #ProductBreakdown SET [Percentage] =
(SELECT 
  [TotalSales] / SUM([TotalSales]) OVER(PARTITION BY Product) * 100 AS PercentageSales
FROM #ProductBreakdown PB2 WHERE PB1.Product = PB2.Product AND PB1.TotalSales = PB2.TotalSales)
FROM #ProductBreakdown PB1

SELECT * FROM #ProductBreakdown

However, this returns 100% for each row.

Does anyone have any advice on what/where I'm going wrong?

CodePudding user response：

You can include your calculation in a CTE and simply update it:

with updateme as (
    select *,
      TotalSales / Sum(TotalSales) over(partition by Product) * 100 as PercentageSales
    from #ProductBreakdown 
)
update updateme set Percentage = PercentageSales;

CodePudding user response：

The mistake you made was that you correlated the TotalSales column in the subquery as well, instead of just Product.

But you can update a CTE or derived table without joins or rescanning the base table

UPDATE pb
SET Percentage = pb.PercentageSales
FROM (
    SELECT *,
      pb.TotalSales / SUM(pb.TotalSales) OVER (PARTITION BY pb.Product) * 100 AS PercentageSales
    FROM #ProductBreakdown pb
) pb;

But I recommend you use a view for this instead anyway, as otherwise you would have to keep it in sync with a trigger.