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
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]
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]
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]
