# PERCENT_RANK of Analytic Functions in Oracle SQL

## PERCENT_RANK of Analytic Functions in Oracle SQL

PERCENT_RANK
PERCENT_RANK is similar to the CUME_DIST function. It return result in between 0 to 1. First row result is zero.

As Aggregate function, calculation done as the rank of row r minus 1 divided by the number of rows in the aggregate group.
As Analytic function, calculation done as the rank of r minus 1, divided by 1 less than the number of rows.

Syntax:
``` PERCENT_RANK (expr) within group (order by expr | desc/asc | nulls | first/last) ```

Examples of PERCENT_RANK Aggregate function:
Example show you the example of percent_rank function and then manually calculation show you how percent_rank function work.
``` SELECT PERCENT_RANK(10000) WITHIN GROUP (ORDER BY salary) AS percent_rank_sal, ROUND(PERCENT_RANK(10000) WITHIN GROUP (ORDER BY salary)*100,2) AS percentwise FROM employees;```

``` ```

```PERCENT_RANK_SAL PERCENTWISE ---------------- ----------- .822429907 82.24 ```
Calculate manually percent_Rank:
``` SQL> select count(*) from employees;```

``` COUNT(*) ---------- 107 SQL> select count(*) from employees where salary select 88/107 from dual; ```

``` 88/107 ---------- .822429907 ```

Example of PERCENT_RANK analytic function
Note: First row result is zero.
``` SELECT employee_id, department_id, salary, PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank_sal, ROUND(PERCENT_RANK() OVER (ORDER BY salary)*100) AS percentwise FROM employees where department_id = 30;```

``` EMPLOYEE_ID DEPARTMENT_ID SALARY PERCENT_RANK_SAL PERCENTWISE ----------- ------------- ---------- ---------------- ----------- 119 30 2500 0 0 118 30 2600 .2 20 117 30 2800 .4 40 116 30 2900 .6 60 115 30 3100 .8 80 114 30 11000 1 100 ```

This site uses Akismet to reduce spam. Learn how your comment data is processed.