Ratio to total

[Guest]

I have a query where I want calculate the ratio of that
line to the total of that colum.
For example:
territory # ratio
1 10
2 20

total #=30. I want to calculate for each line, the ratio
for territory 1=33%, ratio for territory 2=66%

thanks

 

[Dale Fye]

You first need to compute the Sum of the # column (I hope your column
name is not really #, if so, change it to something more meaningful
that does not use a special character). Do this by creating a
subquery that sums that column to a column named total, and name the
resulting table as S(for subquery). Then use a Cartesian join between
your table and this subquery so that each row of the result set will
contain the columns from yourTable plus an additional column (Total)
from S. Then just divide your No column by the Total Column.


SELECT T.Territory, T.No, T.No/S.Total as Ratio
FROM yourTable T,
(SELECT SUM(No) as Total FROM yourTable) as S

--
HTH

Dale Fye


I have a query where I want calculate the ratio of that
line to the total of that colum.
For example:
territory # ratio
1 10
2 20

total #=30. I want to calculate for each line, the ratio
for territory 1=33%, ratio for territory 2=66%

thanks