CODEWITHSUNDEEP
sqlsql-serverselect
Dan Paschevici
Dan Paschevici

Reputation: 1021

SQL Server calculate percents of values for each row

I have a query which return a table something like:

Value    Description
--------------------
12       Decription1
43       Decription2
78       Decription3
3        Decription4
6        Decription5

My select looks like 

select 
    sum(value), description 
from 
    table 
group by 
    description

There are 5 rows and the sum of all 5 rows are 169 which is 100%, now I want to find out how many % is for first row, for second and so on, I know that the formula is for e.g for first row 12*100/169. How to do this in my query.

Thanks!

Upvotes: 0

Views: 57

Answers (4)

DimaSUN
DimaSUN

Reputation: 921

select description , value,pcnt =  100.*value / (sum(value) over ( )) 
    from table

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

You can use windowed functions:

SELECT *,
    [percentage] = ROUND(100 * 1.0 * value / SUM(value) OVER(), 0)
FROM #tab;

LiveDemo

Output:

╔═══════╦═════════════╦════════════╗
║ value ║ Description ║ Percentage ║
╠═══════╬═════════════╬════════════╣
║    12 ║ Decription1 ║          8 ║
║    43 ║ Decription2 ║         30 ║
║    78 ║ Decription3 ║         55 ║
║     3 ║ Decription4 ║          2 ║
║     6 ║ Decription5 ║          4 ║
╚═══════╩═════════════╩════════════╝

Upvotes: 0

Akshey Bhat
Akshey Bhat

Reputation: 8545

declare @total int;
select @total = Sum(Value) from table

select (sum(value) * 100.0)/@total as percentage , description 
    from table 
      group by description`

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can do this using window functions:

select description, sum(value),
       sum(value) * 100.0 / sum(sum(value)) over () as percentage
from table
group by description;

Upvotes: 1

Related Questions