The CUME_DIST () are analytic functions in SQL Server 2012

The purpose of CUME_DIST () function is to calculate cumulative distribution value in a group of the values in a given result set or a partition.

The CUME_DIST () formula

“The values less than or equal to the current value row number” / “Total number of row in a group or partition”. The return type is float (53) and the values are always between 0 and 1.

The syntax of CUME_DIST () is given below.

  1. CUME_DIST ( )
  2. OVER ([ partition_by_clause ] order_by_clause )
Example 1 CUME_DIST ()
  1. CREATE TABLE [CompanyShareRates]
  2. (
  3. [Id] INT IDENTITY,
  4. [Date] DATETIME,
  5. [Name] VARCHAR(100),
  6. [ShareRate] NUMERIC(18,2)
  7. )
  9. INSERT INTO [CompanyShareRates] VALUES('2017-02-03','TCS',200.00)
  10. INSERT INTO [CompanyShareRates] VALUES('2017-02-04','TCS',210.00)
  11. INSERT INTO [CompanyShareRates] VALUES('2017-02-05','TCS',230.00)
  12. INSERT INTO [CompanyShareRates] VALUES('2017-02-06','TCS',230.00)
  13. INSERT INTO [CompanyShareRates] VALUES('2017-02-07','TCS',250.50)
  16. SELECT
  17. ROW_NUMBER() OVER (ORDER BY [Name],[ShareRate]) AS [Row Number]
  18. ,[Date]
  19. ,[NAME]
  20. ,[ShareRate]
  21. ,CUME_DIST () OVER (ORDER BY [Name],[ShareRate]) AS [CUME_DIST]
  22. FROM [CompanyShareRates]

Explanation

CUME_DIST is calculated in Excel.



In ‘TCS’ group given above, we have 4 share rates in 5 rows.

Column E

For the 1st row, there is 1 row with value (200) or lower. Hence, the ‘E’ Column value will be 1.

For the 2nd row, there are 2 rows with value (210) or lower. Hence, the ‘E’ Column value will be 2.

For the 3rd and 4th rows, there are 4 rows with value (230) or lower. Hence, the ‘E’ Column value will be 4 for both the row.

For the 5th row, there are 5 rows with the value (250.5) or lower. Hence, the ‘E’ Column value will be 5.

Column F

Total number of rows in result set. i.e. -- 5

Column G
  • CMUE_DIST for row 1 = 1/5 = 0.2
  • CMUE_DIST for row 2 = 2/5 = 0.4
  • CMUE_DIST for row 3 = 4/5 = 0.8
  • CMUE_DIST for row 4 = 4/5 = 0.8
  • CMUE_DIST for row 5 = 5/5 = 1
Example 2 CUME_DIST () with Partition by Clause
  1. INSERT INTO [CompanyShareRates] VALUES('2017-02-03','Hero Motocorp',390.50)
  2. INSERT INTO [CompanyShareRates] VALUES('2017-02-03','GAIL',302.00)
  3. INSERT INTO [CompanyShareRates] VALUES('2017-02-04','Hero Motocorp',391.00)
  4. INSERT INTO [CompanyShareRates] VALUES('2017-02-04','GAIL',302.25)
  5. INSERT INTO [CompanyShareRates] VALUES('2017-02-05','Hero Motocorp',390.00)
  6. INSERT INTO [CompanyShareRates] VALUES('2017-02-05','GAIL',203.25)
  7. INSERT INTO [CompanyShareRates] VALUES('2017-02-06','Hero Motocorp',389.50)
  8. INSERT INTO [CompanyShareRates] VALUES('2017-02-06','GAIL',401.00)
  9. INSERT INTO [CompanyShareRates] VALUES('2017-02-07','Hero Motocorp',389.00)
  10. INSERT INTO [CompanyShareRates] VALUES('2017-02-07','GAIL',401.00)
  13. SELECT
  14. ROW_NUMBER() OVER (Partition by Name ORDER BY [Name],[ShareRate]) AS [Row Number]
  15. ,[Date]
  16. ,[NAME]
  17. ,[ShareRate]
  18. ,CUME_DIST () OVER (Partition by Name ORDER BY [Name],[ShareRate]) AS [CUME_DIST]
  19. FROM [CompanyShareRates]