Ask the Expert

Calculating percentiles in SQL

I'm trying to calculate percentiles in SQLPLUS on Oracle8i. There doesn't seem to be a built-in function for this. What is the best way to calculate quartiles in SQLPLUS.

    Requires Free Membership to View

I will answer your question using standard SQL, so more than just the Oracle users who read this can benefit. Aside from the NUMBER data type, the SQL in my example should be portable.

A percentile is a value on a scale of one hundred that indicates the percent of a distribution that is equal to or below it. In SQL, for every row, we want to find the number of rows that have values less than or equal to its value. That count, divided by the count of all the rows, is the percentile. This is best illustrated with an example. Suppose we have the results of a college exam stored in a table:

create table ExamResults
( Student varchar(15),
Score number(3)
);

insert into ExamResults values ('Lenny',94);
insert into ExamResults values ('Ralph',93);
insert into ExamResults values ('Joe',91);
insert into ExamResults values ('Mary',80);
insert into ExamResults values ('Frank',89);
insert into ExamResults values ('Susan',89);
insert into ExamResults values ('Bill',74);
insert into ExamResults values ('Ben',99);
insert into ExamResults values ('Fred',66);
insert into ExamResults values ('George',100);
insert into ExamResults values ('Walter',90);
insert into ExamResults values ('Betty',89);
insert into ExamResults values ('Heather',83);
insert into ExamResults values ('Richard',91);
commit;
We could get our percentiles with SQL like this:
select a.*, round( 100.0 * 
( select count(*) 
from ExamResults b 
where b.Score <= a.Score ) /
total.cnt
, 1) percentile
from ExamResults a 
cross join 
( select count(*) cnt 
from ExamResults ) total;
Here are the results ordered by score in descending order:
STUDENT              SCORE PERCENTILE
--------------- ---------- ----------
George                 100        100
Ben                     99       92.9
Lenny                   94       85.7
Ralph                   93       78.6
Joe                     91       71.4
Richard                 91       71.4
Walter                  90       57.1
Frank                   89         50
Susan                   89         50
Betty                   89         50
Heather                 83       28.6
Mary                    80       21.4
Bill                    74       14.3
Fred                    66        7.1
The cross join allows us to take the count of all rows and use it in a calculation on every row. Because the sub-query always returns one row, the cross join will return one and only one row per row found in the ExamResults table. If you are using a SQL engine that does not support SQL-92 joins, you should be able to replace the "cross join" in the statement with a comma and get the same results. Since you are using Oracle, I should note that Oracle 8i does not support SQL-92 joins, however, 9i does.

For More Information

  • What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was first published in February 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: