SQL CUME_DIST

The SQL Server CUME_DIST is one of the Analytic Functions, which is used to calculate the cumulative distribution of rows in a partition or entire row set. The basic syntax of the SQL CUME_DIST is as shown below:

SELECT CUME_DIST() 
           OVER ( PARTITION_BY_Clause 
                  ORDER_BY_Clause
                )
FROM [Source]
  • Partition_By_Clause: It divides the records selected by the SELECT Statement into partitions.
    • If you give it, then the CUME_DIST function will calculate the cumulative distribution for the rows in each partition.
    • If you haven’t specified, then it will consider all rows as a single partition.
  • Order_By_Clause: This Server clause is useful to sort the Partitioned data into a specified order. Please refer to Order By Clause.

We are going to use the below-shown data for this SQL CUME_DIST demonstration

Source Table 0

SQL CUME_DIST without Partition By Clause

In this example, we show you what happens if we avoid using the Partition By Clause in CUME_DIST Function.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,CUME_DIST() OVER (ORDER BY [Sales] ASC) AS Cumulative 
  FROM [Employee]
SQL CUME_DIST Example 2

Let me change the SQL Server CUME_DIST Order by clause from Ascending order to Descending.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,CUME_DIST() OVER (ORDER BY [Sales] ASC) AS Cumulative 
  FROM [Employee]
Order by Descending 3

CUME_DIST with Partition By Example

This example explains how to return the SQL Server cumulative distribution for the partitioned records present in a table. The following Query will categorize the data by Occupation using their Sales amount. And then written the cumulative distribution for each category independently.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,CUME_DIST() OVER ( PARTITION BY [Occupation]
			  ORDER BY [Sales] ASC
			) AS Cumulative
  FROM [Employee]
SQL CUME_DIST 4

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.