# Thread: find percentage of a field

1. Registered User
Join Date
Jun 2004
Location
Universe of Materials
Posts
4

## Unanswered: find percentage of a field

HOW can i get the percentage for each rank?
http://shahabedeen.europe.webmatrixh...rsentile04.gif

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Oh goody, I love this kind of problem!
Code:
`SELECT 1e2 * a.rank / (SELECT Sum(b.rank) FROM tblpoll AS b) FROM tblpoll AS a`
The problem is that you need to cooerce the type change before you start doing the math instead of after the math is complete. I'll let you work out the details!

-PatP

3. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Pat, will the optimizer be smart enough to only run the subquery once? Probably, but I'd phrase it this way to be sure:

from tblpoll,
(SELECT Sum(rank) as TotalVotes FROM tblpoll) subquery

"1e2"? 100, right?

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by blindman
Pat, will the optimizer be smart enough to only run the subquery once? Probably, but I'd phrase it this way to be sure:

from tblpoll,
(SELECT Sum(rank) as TotalVotes FROM tblpoll) subquery

"1e2"? 100, right?
As long as the subquery isn't correlated, the optimizer will only run it once.

Yes, 1e2 is a floating point 100. In this case, the type is very important because you have to force the type coersion to occur before the division, not after it if you want to retain the fractional portion of the result.

-PatP

5. Registered User
Join Date
Jun 2004
Location
Universe of Materials
Posts
4
Thanks pat...

6. Registered User
Join Date
Jun 2004
Location
Universe of Materials
Posts
4
Dear guy,I have a new problem:
http://shahabedeen.europe.webmatrixh...rsentile05.gif
As u can see the float point is not beautiful enough,How can I reduce the float points to 2 number?

7. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579