Q
Problem solve Get help with specific problems with your technologies, process and projects.

# 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.

## SearchDataManagement

• ### Why consider an augmented data catalog?

Automated and augmented data catalogs have been around for a few years, but adoption is still lagging. Find out why an enterprise...

• ### Alation brings data catalog technology to the public cloud

Alation's data intelligence technology is getting easier for organizations to use, with a new managed service that can help ...

• ### Yellowbrick Manager embraces Kubernetes for data warehouse

Yellowbrick is building out a new unified control plane to help users manage distributed cloud data warehouse deployments. The ...

• ### Air Force's data overhaul makes analytics a priority

With its data locked in legacy systems that didn't communicate with one another, in 2017 the Air Force started to overhaul of its...

• ### 6 reasons you may need data science as a service

There are plenty of reasons to outsource all or part of a data science project to a service. Find out how enterprises are using ...

• ### Logi Analytics acquired by ERP vendor Insightsoftware

Logi Analytics, a BI vendor specializing in embedded business intelligence, was acquired on April 7 by an ERP vendor specializing...

## SearchSAP

• ### Unpatched applications threaten SAP security

Cyberattacks are a significant threat to unpatched, unprotected SAP applications, according to a new threat intelligence report ...

• ### Understand your S/4HANA deployment options

SAP deployment options can be confusing, particularly with name changes. Learn which SAP program is right for your company based ...

• ### MSC Industrial Supply transforms to SAP S/4HANA private cloud

MSC Industrial Supply needed to cut costs and modernize its ERP system, so it first moved to S/4HANA on a hybrid cloud and has ...

## SearchSQLServer

• ### SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• ### SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• ### Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

## TheServerSide.com

• ### Microsoft previews OpenJDK distro to the delight of devs

In a move meant to attract more Java developers to its Azure cloud and further support the Java community, Microsoft launched a ...

• ### Supreme Court ruling on Java APIs eases developer worries

Now that the Supreme Court has ruled for Google over Oracle in their high-stakes copyright battle over Java APIs, developers can ...

• ### Genuitec's CodeTogether 4.0 promotes pair programming

With Genuitec CodeTogether 4.0, development teams can work collaboratively even when remote. The product supports pair ...

## SearchDataCenter

• ### New Intel Ice Lake processors boost performance, security

Intel launches third-generation Xeon Scalable processors that bolster security, accelerate common data center workloads by 46% on...

• ### IBM tools speed mainframe application modernization projects

IBM has released new versions of its application modernization tools designed to bring its Z series of mainframe applications in ...

• ### Nvidia vs. AMD: Compare GPU offerings

Chip vendors Nvidia and AMD each offer GPUs optimized for large data centers. Compare the two to decide which best suits your ...

## SearchContentManagement

As the pandemic disrupts paper workflows, Adobe courts small business users with simple webforms, digital signatures and payments...

• ### Know when to use a headless CMS and when it's not worth it

Headless CMSes aren't a silver bullet for every circumstance. Evaluate three solid use cases for a headless CMS and three ...

• ### 8 business benefits and challenges of video conferencing

Remote work necessitates software such as video conferencing software. Consider both the business benefits and the challenges ...

## SearchHRSoftware

• ### Hybrid workplace may give employers hiring edge

The post-pandemic office is leaning strongly in the direction of hybrid workplaces. Job candidates view working from home as a ...

• ### Biden infrastructure plan may deliver huge training boost

President Biden's \$2T infrastructure plan will likely result in training programs for rapid reskilling, especially in the ...

• ### 10 learning and development trends for the next normal

Today's companies need to upskill and reskill their workforces to stay competitive. Here's a look some of the most important ...

Close