Q

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?

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.
 

This was first published in February 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

1 comment

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close