Percentage breakdown

Question

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

Product	TotalSales	PercentageSales
Product A	596.42	40.83
Product A	127.55	8.73
Product A	736.83	50.44
Product B	379.51	64.82
Product B	205.95	35.18
Product C	905.99	100.00

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?

Answer 1

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.