how do i cpmpute deciles/quartiles using SQL (embedded in RPG)

Iluvsnow used Ask the Experts™

lets say we have 100,000 accounts each with a collectibility score. of course there might be a lot of accounts that have the same score and some that have 0 score. i would like a SQL formula to compute deciles/twentiles. the scores range from 0 to 7000.

so that in the end we know that based on this universe eg scores 6500-7000 is the 20th and 5000-6499 is 19th twentile and 0-500 is 1st twnetile etc etc

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SharathData Engineer

Did not understand the question. can you provide some sample input and expected result.
Here is how to do quartiles:

Just multiply by 10 instead of 4 for deciles
i think i answered the question
As instructed by AngelIII, here is a copy of the code found at that website modified to return deciles
select a.*, ceiling( 10.0 * 
                     ( select count(*) 
                         from ExamResults  
                         where Score <= a.Score ) /
                     ( select count(*) cnt 
                         from ExamResults )
                   ) quartile
  from ExamResults a;

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial