How can I calculate percentage column

Question

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?

Answer 1

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

Answer 2

You can update the Percentage column like so:

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

Fiddle example

Answer 3

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