Sorry, I've had to adjust my question.

Using SQL Server 2016, I have the summated results from a query into a new table, which looks like this:

Product	Total Sales	Percentage
Product A	596.42	0.00
Product A	127.55	0.00
Product A	736.83	0.00
Product B	379.51	0.00
Product B	205.95	0.00
Product C	905.99	0.00

I added the Percentage column so I could update that column with the percentage based on the overall total sales for each Product.

For example, I'm trying to achieve the below percentage results from the above table.

Product	Total Sales	Percentage
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

How do I achieve this with an UPDATE statement?

CodePudding user response：

This may depend on your DBMS, if supported you can use OVER() to sum up all the rows

SELECT 
  TOTAL_SALES / SUM(TOTAL_SALES) OVER() AS PERCENT_OF_SALES
FROM TABLE

If unsupported you can use a subquery

SELECT 
  TOTAL_SALES / (SELECT SUM(TOTAL_SALES) FROM TABLE) AS PERCENT_OF_SALES
FROM TABLE

For an update statement depending on your DBMS you can do something like

DECLARE @d INT = (SELECT SUM(TOTAL_SALES) FROM TABLE)

UPDATE TABLE
SET PERCENT_OF_SALES = SALES / @d

CodePudding user response：

You can update the Percentage column like so:

update tbl
set Percentage = ([Total Sales]/(select sum([Total Sales]) from tbl)) * 100

Fiddle example

CodePudding user response：

With Mysql :

SELECT SUM(total) FROM test INTO @tot;
UPDATE test SET percent = 100 * total / @tot ;

DBFiddle example : https://www.db-fiddle.com/f/hERoW3LajH9tMhjQJUKHER/1