SQL CUME_DIST

The SQL CUME_DIST is one of the Analytic Function in SQL Server, which is used to calculate the cumulative distribution of row in a partition, or in an entire row set. This article will show you, How to use the analytic function called CUME_DIST in SQL Server with example. Before we get into the example, let us see the syntax behind this:

SQL CUME_DIST Syntax

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 divide the records selected by the SELECT Statement into partitions.
  • If you specified the Partition By Clause, then CUME_DIST function will calculate the cumulative distribution for the rows in each partition.
  • If you haven’t specified the Partition By Clause, then CUME_DIST function will consider all rows as a single partition.
• Order_By_Clause: This clause is used to sort the Partitioned data into specified order. Please refer SQL Order By Clause for better understanding.

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

SQL CUME_DIST without Partition By Clause

In this example we will show you, What happen if we avoid using the Partition By Clause in CUME_DIST Function.

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

SQL CUME_DIST with Partition By Example

In this example we will show you, How to return the 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

Thank You for Visiting Our Blog