SQL CUME_DIST Function

Calculate the cumulative distribution of the product prices.

SELECT ProductName, UnitPrice,
       CUME_DIST() OVER(ORDER BY UnitPrice) AS 'Price Distribution'
  FROM Product
 ORDER BY UnitPrice

ProductName	UnitPrice	Price Distribution
Geitost	2.50	0.0128205128205128
Guaraná Fantástica	4.50	0.0256410256410256
Konbu	6.00	0.0384615384615385
Filo Mix	7.00	0.05128205128205128
Sir Rodney's Marmalade	81.00	0.9615384615384616
Mishi Kobe Niku	97.00	0.974358974358974
Thüringer Rostbratwurst	123.79	0.987179487179487
Côte de Blaye	263.50	1

Note: The cumulative distribution starts low and goes up to 1 (the last row).

Syntax of the CUME_DIST function.

CUME_DIST() 
  OVER( PARTITION BY expression, ...
        ORDER BY expression, ... )

SELECT MONTH(OrderDate) AS Month, 
       CONCAT(FirstName, ' ', LastName) AS Customer, TotalAmount, 
       CUME_DIST() OVER(PARTITION BY MONTH(OrderDate)
                        ORDER BY TotalAmount) AS 'Sales Distribution'
  FROM [Order] O
  JOIN Customer C ON O.CustomerId = C.Id
 WHERE YEAR(OrderDate) = 2013
 ORDER BY MONTH(OrderDate), TotalAmount

Order Month	Customer	TotalAmount	Sales Distribution
1	Paolo Accorti	49.80	0.030303030303030304
1	Yoshi Latimer	102.40	0.06060606060606061
1	Maurizio Moroni	192.00	0.09090909090909091
1	Roland Mendel	5796.00	0.9393939393939394
1	Jytte Petersen	11283.20	0.9696969696969697
1	Jean Fresnière	11493.20	1
2, 3, 4, ... 11
12	Patricio Simpson	12.50	0.0208333333333333
12	Paolo Accorti	18.40	0.0416666666666667
12	Pascale Cartrain	28.00	0.0625
12	Martín Sommer	4035.80	0.958333333333333
12	Marias Larsson	4337.00	0.979166666666667
12	Roland Mendel	6984.50	1

SELECT MONTH(OrderDate) AS 'Month', 
       SUM(TotalAmount) AS 'Monthly Sales', 
       CUME_DIST() OVER(ORDER BY SUM(TotalAmount)) AS CumeDist
  FROM [Order]
 WHERE YEAR(OrderDate) = 2013
 GROUP BY MONTH(OrderDate)
 ORDER BY MONTH(OrderDate)

Note: 67% of the sales from year 2013 are greater than 50,000.

• Our Sql Window Functions Reference
• Our Sql PERCENT_RANK Function Reference
• Our Sql JOIN Tutorial