Calculate percentage value for each row based on total column value in SQL Server

Question

I have a table like below. I need to calculate percentage value for each row based on total column value. How to achieve this with a SQL query?

I am expecting results like this:

Please find the below simple calculation to get result.

(100 / 1000) * 100 = 10

for the first value.

(row value / grand total) * 100

Can you please help me to get 10 as result for the first row value with a SQL query? Thanks in advance.

Answer 1

You can use the SUM() function as analytic function over the entire table to compute the total sum. Then, just divide each region's cost by that sum to obtain the percentage.

SELECT
    Region,
    Cost,
    100 * Cost / SUM(Cost) OVER () AS Percentage
FROM yourTable

Note that you could have also used a non correlated subquery to find the total cost, e.g.

(SELECT SUM(Cost) FROM yourTable)

But the first version I gave you might outperform if for no other reason than it requires running only a single query.

Output:

Demo here:

Rextester

Update:

For your updated query I might use the following:

WITH cte AS (
    SELECT
        Region,
        SUM(Cost) AS sum_cost
    FROM yourTable
    GROUP BY Region
)

SELECT
    Region,
    sum_cost,
    100 * sum_cost / SUM(sum_cost) OVER () AS Percentage
FROM cte;

Demo

Answer 2

You can get a result with below query.

;with Sales(region,cost,Total)
    as
    (
    select region,cost,
    (sum(cost) over()) as Total
    from YourTable
    )
    Select Region,Cost,convert(numeric(18,0),Cost/1000*100) as Per from sales