The SQL CUME_DIST is one of the Analytic Functions in SQL Server, which is used to calculate the cumulative distribution of row in a partition or entire row set.
The basic syntax of the CUME_DIST in SQL Server 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 given the Partition By Clause, then the CUME_DIST function will calculate the cumulative distribution in SQL Server for the rows in each partition.
- If you haven’t specified the Partition By Clause, then the CUME_DIST function will consider all rows as a single partition.
- Order_By_Clause: This SQL Server clause is used to sort the Partitioned data into a specified order. Please refer to SQL Order By Clause.
We are going to use the below-shown data for this CUME_DIST demonstration
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 in SQL server.
USE [SQL Tutorial] GO SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,CUME_DIST() OVER (ORDER BY [Sales] ASC) AS Cumulative FROM [Employee]
OUTPUT
Let me change the Order by clause from Ascending order to Descending.
USE [SQL Tutorial] GO SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,CUME_DIST() OVER (ORDER BY [Sales] ASC) AS Cumulative FROM [Employee]
OUTPUT
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 partition the data by Occupation using their Sales amount, and then written the cumulative distribution for each partition independently.
USE [SQL Tutorial] GO SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,CUME_DIST() OVER ( PARTITION BY [Occupation] ORDER BY [Sales] ASC ) AS Cumulative FROM [Employee]
OUTPUT