in a cross table result is very common to add a percentage column in respect
of the sum of another column
for example
x z%
a 2 0,46 = 2/(2+5+6)
b 5 0,38 = 5/(2+5+6)
c 6 0,15 = 6/(2+5+6)
is there an easy way to do that in t-sql using the pivot command ?
This was going to be my first answer
"Not easily that I can think of because it requires knowledge of the other rows in the result set and that is where SQL is not very good. That is performing calculations at two levels one aggregate is across th
You need to find the total and then calculate the percentage. This is normally done using a derived table"
However in digging I looked into windowing. This is a new feature that can be used with the new ranking functions as well as the normal aggregates.
In your situation you can do
select x, 1.0*x / sum(x) over (parition by 1) percentage
from mytable
What this does is that it performs a sum of x over the partition of 1 (as this is the same for each row it does a sum across the whole resultset).
