**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 `CUME_DIST()`

function.

## Introduction to Oracle `CUME_DIST()`

function

Sometimes, you want to pull the top or bottom x% values from a data set e.g., top 5% salesman by volume. To do this, you can use the Oracle `CUME_DIST()`

function.

The `CUME_DIST()`

function is an analytic function that calculates the cumulative distribution of a value in a set of values. The result of `CUME_DIST()`

is greater than 0 and less than or equal to 1. Tie values evaluate to the same cumulative distribution value.

The following shows the syntax of the Oracle `CUME_DIST()`

function:

`CUME_DIST() OVER ( [ query_partition_clause ] order_by_clause )`

Because `CUME_DIST()`

function is order sensitive, the `order_by_clause`

is required. The `order_by_clause`

has the following syntax:

`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 form:

`PARTITION BY expression1 [,expression2,..]`

The `PARTITION BY`

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

## Oracle `CUME_DIST()`

examples

### Using Oracle `CUME_DIST()`

function over the result set example

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

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

Here is the result:

As shown in the output, 33.33 % of salesman have sales amount greater than 1.99 million.

### Using Oracle `CUME_DIST()`

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(CUME_DIST() OVER (
PARTITION BY year
ORDER BY sales DESC
) * 100,2) || '%' cume_dist
FROM
salesman_performance
WHERE
year in (2016,2017);
```

Here is the output:

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 in descending order to which the`CUME_DIST()`

function is applied.

To get top 30% of the salesman by sales revenue in 2016 and 2017, you use the following query:

```
WITH cte_sales AS (
SELECT
salesman_id,
year,
sales,
ROUND(CUME_DIST() OVER (
PARTITION BY year
ORDER BY sales DESC
),2) cume_dist
FROM
salesman_performance
WHERE
year in (2016,2017)
)
SELECT
*
FROM
cte_sales
WHERE
cume_dist <= 0.30;
```

The output is:

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

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