Export (0) Print
Expand All

CUME_DIST (Transact-SQL)

Calculates the cumulative distribution of a value in a group of values in SQL Server 2012. That is, CUME_DIST computes the relative position of a specified value in a group of values. For a row r, assuming ascending ordering, the CUME_DIST of r is the number of rows with values lower than or equal to the value of r, divided by the number of rows evaluated in the partition or query result set. CUME_DIST is similar to the PERCENT_RANK function.

CUME_DIST( )
    OVER ( [ partition_by_clause ] order_by_clause )

OVER ( [ partition_by_clause ] order_by_clause)

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the logical order in which the operation is performed. order_by_clause is required. The <rows or range clause> of the OVER syntax cannot be specified in a CUME_DIST function. For more information, see OVER Clause (Transact-SQL).

The range of values returned by CUME_DIST is greater than 0 and less than or equal to 1. Tie values always evaluate to the same cumulative distribution value. NULL values are included by default and are treated as the lowest possible values.

The following example uses the CUME_DIST function to compute the salary percentile for each employee within a given department. The value returned by the CUME_DIST function represents the percent of employees that have a salary less than or equal to the current employee in the same department. The PERCENT_RANK function computes the percent rank of the employee's salary within a department. The PARTITION BY clause is specified to partition the rows in the result set by department. The ORDER BY clause in the OVER clause logically orders the rows in each partition. The ORDER BY clause in the SELECT statement determines the display order of the result set.

USE AdventureWorks2012;
GO
SELECT Department, LastName, Rate, 
       CUME_DIST () OVER (PARTITION BY Department ORDER BY Rate) AS CumeDist, 
       PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate ) AS PctRank
FROM HumanResources.vEmployeeDepartmentHistory AS edh
    INNER JOIN HumanResources.EmployeePayHistory AS e  
    ON e.BusinessEntityID = edh.BusinessEntityID
WHERE Department IN (N'Information Services',N'Document Control') 
ORDER BY Department, Rate DESC;

Here is the result set.

Department             LastName               Rate                  CumeDist               PctRank
---------------------- ---------------------- --------------------- ---------------------- ----------------------
Document Control       Arifin                 17.7885               1                      1
Document Control       Norred                 16.8269               0.8                    0.5
Document Control       Kharatishvili          16.8269               0.8                    0.5
Document Control       Chai                   10.25                 0.4                    0
Document Control       Berge                  10.25                 0.4                    0
Information Services   Trenary                50.4808               1                      1
Information Services   Conroy                 39.6635               0.9                    0.888888888888889
Information Services   Ajenstat               38.4615               0.8                    0.666666666666667
Information Services   Wilson                 38.4615               0.8                    0.666666666666667
Information Services   Sharma                 32.4519               0.6                    0.444444444444444
Information Services   Connelly               32.4519               0.6                    0.444444444444444
Information Services   Berg                   27.4038               0.4                    0
Information Services   Meyyappan              27.4038               0.4                    0
Information Services   Bacon                  27.4038               0.4                    0
Information Services   Bueno                  27.4038               0.4                    0
(15 row(s) affected)

Community Additions

ADD
Show:
© 2014 Microsoft