# MySQL CUME_DIST Function

Summary: in this tutorial, you will learn how to use the MySQL `CUME_DIST()`

function to calculate cumulative distribution value.

## Overview of MySQL `CUME_DIST()`

Function

The `CUME_DIST()`

is a window function that returns the cumulative distribution of a value within a set of values. It represents the number of rows with values less than or equal to that row’s value divided by the total number of rows.

The returned value of the `CUME_DIST()`

function is greater than zero and less than or equal one (0 < `CUME_DIST()`

<= 1). The repeated column values receive the same `CUME_DIST()`

value.

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

function:

`CUME_DIST() OVER ( PARTITION BY expr, ... ORDER BY expr [ASC | DESC], ... )`

In this syntax, the `PARTITION BY`

clause divides the result set returned by the `FROM`

clause into partitions to which the `CUME_DIST()`

function applies.

The `ORDER BY`

the clause specifies the logical order of the rows in each partition or the whole result set in case the `PARTITION BY`

is omitted. The `CUME_DIST()`

the function calculates the cumulative distribution value of each row based on its order in the partition.

The approximate formula of the `CUME_DIST()`

function is as follows:

`ROW_NUMBER() / total_rows`

## MySQL `CUME_DIST()`

function example

Let’s create a table named `scores`

with some sample data for the demonstration:

```
CREATE TABLE scores (
name VARCHAR(20) PRIMARY KEY,
score INT NOT NULL
);
INSERT INTO
scores(name, score)
VALUES
('Smith',81),
('Jones',55),
('Williams',55),
('Taylor',62),
('Brown',62),
('Davies',84),
('Evans',87),
('Wilson',72),
('Thomas',72),
('Johnson',100);
```

The following statement finds the cumulative distribution on the score in the result set:

```
SELECT
name,
score,
ROW_NUMBER() OVER (ORDER BY score) row_num,
CUME_DIST() OVER (ORDER BY score) cume_dist_val
FROM
scores;
```

Here is the output:

In this example, the score is sorted in ascending order from 55 to 100. Note that the `ROW_NUMBER()`

the function was added for reference.

So how the `CUME_DIST()`

the function performs calculation?

For the first row, the function finds the number of rows in the result set, which has a value less than or equal to 55. The result is 2. Then `CUME_DIST()`

function divides 2 by the total number of rows which is 10: 2/10. the result is 0.2 or 20%. The same logic is applied to the second row.

For the third row, the function finds the number of rows with values less than or equal to 62. There are four rows. Then the result of the `CUME_DIST()`

function is: 4/10 = 0.4 which is 40%.

The same calculation logic is applied to the remaining rows.

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

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

## 0 Comments