Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Location
    Universe of Materials
    Posts
    4

    Red face Unanswered: find percentage of a field

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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Pat, will the optimizer be smart enough to only run the subquery once? Probably, but I'd phrase it this way to be sure:

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

    "1e2"? 100, right?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote 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:

    SELECT rank/TotalVotes
    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. #5
    Join Date
    Jun 2004
    Location
    Universe of Materials
    Posts
    4
    Thanks pat...

  6. #6
    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. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Round()

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •