SQL Server CUME_DIST Function

Summary: in this tutorial, you will learn how to use the SQL Server CUME_DIST() function to calculate a cumulative distribution of a value within a group of values.

Introduction to SQL Server CUME_DIST() function #

Sometimes, you want to make a report that contains the top or bottom x% values from a data set e.g., top 5% sales staffs by net sales. One way to achieve this with SQL Server is to use the CUME_DIST() function.

The CUME_DIST() function calculates the cumulative distribution of a value within a group of values. Simply put, it calculates the relative position of a value in a group of values.

The following shows the syntax of the CUME_DIST() function:

 CUME_DIST() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)
Code language: SQL (Structured Query Language) (sql)

Let’s examine this syntax in detail.

PARTITION BY clause #

The PARTITION BY clause distributes rows into multiple partitions to which the CUME_DIST() function is applied.

The PARTITION BY clause is optional. The CUME_DIST() function will treat the whole result set as a single partition if you omit the PARTITION BY clause.

ORDER BY clause #

The ORDER BY clause specifies the logical order of rows in each partition to which the CUME_DIST() function is applied. The ORDER BY clause considers NULL values as the lowest possible values.

Return value #

The result of CUME_DIST() is greater than 0 and less than or equal to 1.

0 < CUME_DIST() <= 1
Code language: SQL (Structured Query Language) (sql)

The function returns the same cumulative distribution values for the same tie values.

SQL Server CUME_DIST() examples #

Let’s take some examples of using the CUME_DIST() function.

Using SQL Server CUME_DIST() function over a result set example #

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

SELECT 
    CONCAT_WS(' ',first_name,last_name) full_name,
    net_sales, 
    CUME_DIST() OVER (
        ORDER BY net_sales DESC
    ) cume_dist
FROM 
    sales.vw_staff_sales t
INNER JOIN sales.staffs m on m.staff_id = t.staff_id
WHERE 
    year = 2017;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

As shown in the output, 50% of the sales staff have net sales greater than 285K.

Using SQL Server CUME_DIST() function over a partition example #

This example uses the CUME_DIST() function to calculate the sales percentile for each sales staff in 2016 and 2017.

SELECT 
    CONCAT_WS(' ',first_name,last_name) full_name,
    net_sales, 
    year,
    CUME_DIST() OVER (
        PARTITION BY year
        ORDER BY net_sales DESC
    ) cume_dist
FROM 
    sales.vw_staff_sales t
INNER JOIN sales.staffs m on m.staff_id = t.staff_id
WHERE 
    year IN (2016,2017);
Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this example:

• The PARTITION BYclause distributed the rows into two partitions by year, 2016 and 2017.
• The ORDER BY clause sorted rows in each partition by net sales from high to low to which the CUME_DIST() function is applied.

To get the top 20% of sales staff by net sales in 2016 and 2017, you use the following query:

WITH cte_sales AS (
    SELECT 
        CONCAT_WS(' ',first_name,last_name) full_name,
        net_sales, 
        year,
        CUME_DIST() OVER (
            PARTITION BY year
            ORDER BY net_sales DESC
        ) cume_dist
    FROM 
        sales.vw_staff_sales t
        INNER JOIN sales.staffs m  
            ON m.staff_id = t.staff_id
    WHERE 
        year IN (2016,2017)
)
SELECT 
    * 
FROM 
    cte_sales
WHERE 
    cume_dist <= 0.20;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the output:

In this tutorial, you have learned how to use the SQL Server CUME_DIST() function to calculate the cumulative distribution of a value in a group of values.