**Summary**: in this tutorial, you will learn how to calculate a cumulative distribution of a value in a set of values by using the Oracle `PERCENT_RANK()`

function.

## Introduction to Oracle `PERCENT_RANK()`

function

The `PERCENT_RANK()`

function is similar to the `CUME_DIST()`

function. The `PERCENT_RANK()`

function calculates the cumulative distribution of a value in a set of values. The result of `PERCENT_RANK()`

function is between 0 and 1, inclusive. Tie values evaluate to the same cumulative distribution value.

The following illustrates the syntax of the Oracle `PERCENT_RANK()`

function:

Code language: SQL (Structured Query Language) (sql)`PERCENT_RANK() OVER ( [ query_partition_clause ] order_by_clause )`

Because `PERCENT_RANK()`

is order sensitive, the `order_by_clause`

is required. The `order_by_clause`

has the following form:

Code language: SQL (Structured Query Language) (sql)`ORDER BY expression1 [ASC | DESC ] [NULLS FIRST | LAST] [, expression2 [ASC | DESC ] [NULLS FIRST | LAST],... ]`

The `ORDER BY`

clause specifies the order of rows in each partition.

The `query_partition_clause`

has the following syntax:

Code language: SQL (Structured Query Language) (sql)`PARTITION BY expression1 [,expression2,..]`

The `PARTITION BY`

clause divides the result set into multiple partitions. It is an optional clause. Omitting this clause means that the function will treat the whole result set as a single partition.

## Oracle `PERCENT_RANK()`

examples

### Using Oracle `PERCENT_RANK()`

function over the result set example

The following statement calculates the sales percentile for each salesman in 2017:

```
SELECT
salesman_id,
sales,
ROUND(
PERCENT_RANK() OVER (
ORDER BY sales DESC
) * 100,2) || '%' percent_rank
FROM
salesman_performance
WHERE
YEAR = 2017;
```

Code language: SQL (Structured Query Language) (sql)

The following picture shows the result:

### Using Oracle `PERCENT_RANK()`

function over the partition example

The following statement calculates the sales percentile for each salesman in 2016 and 2017.

```
SELECT
salesman_id,
year,
sales,
ROUND(PERCENT_RANK() OVER (
PARTITION BY year
ORDER BY sales DESC
) * 100,2) || '%' percent_rank
FROM
salesman_performance
WHERE
year in (2016,2017);
```

Code language: SQL (Structured Query Language) (sql)

The output is:

In this example:

- The
`PARTITION BY`

clause divided the result set into two partitions by year, 2016 and 2017. - The
`ORDER BY`

clause sorted rows in each partition by sales amount from high to low. - The
`PERCENT_RANK()`

function was then applied to the value of each row in each partition.

In this tutorial, you have learned how to use the Oracle `PERCENT_RANK()`

function to calculate the cumulative distribution of a value in a set of values.