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

)

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

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`

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

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`

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

## 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 INTOscores(name, score)

VALUES

(‘Smith’,81),

(‘Jones’,55),

(‘Williams’,55),

(‘Taylor’,62),

(‘Brown’,62),

(‘Davies’,84),

(‘Evans’,87),

(‘Wilson’,72),

(‘Thomas’,72),

(‘Johnson’,100);

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

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;

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

Here is the output:

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

function was added for reference.

So how the `CUME_DIST()`

function performs calculation?

For the first row, the function finds the number of rows in the result set, which have 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 the 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.