CUME_DIST() function syntax with an example in SQL Server

CUME_DIST() function syntax with an example in SQL Server

·        

Introduction: 

In my previous posts we have seen what is Transact-Sql in sql server and What are new top features of T-Sql in server. This is one of the best sql server interview Questions and answers. Watch more ASP.NET, C#.NET, Jquery, Sql Server, HTML5, HTML, CSS, JQUERY UI Interview questions from here. 

Description: 

This function provides cumulative distribution value.

Example:

USE AdventureWorks
GO
SELECT SalesOrderID, OrderQty,
CUME_DIST() OVER(ORDER BY SalesOrderID) AS CDist
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY CDist DESC
GO




Result:



SalesOrderID	OrderQty	Cdist
43670	1	1
43670	2	1
43670	2	1
43670	1	1
43669	1	0.6
43667	3	0.5
43667	1	0.5
43667	1	0.5
43667	1	0.5
43663	1	0.1

Here, rows having SalesOrderID=43670 are ‘4’.

Rows having SalesOrderID<43670 are ‘6’.

So, total count of rows is 10.

The logic for CUME_DIST() for SalesOrderID ‘43670’ is (4+6)/10 =1

So, Cumulative distribution for SalesOrderID ‘43670’ is 1.

If you have any queries or suggestions, please feel free to ask in comments section.