SQL CUME_DIST 4

SQL CUME_DIST

by

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 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 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]
SQL CUME_DIST 2

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]
SQL CUME_DIST 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 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]
SQL CUME_DIST 4