If you’re getting an error that reads “The function ‘CUME_DIST’ must have an OVER clause” when using SQL Server, it’s because you’re calling the CUME_DIST() function without an OVER clause.

The CUME_DIST() function requires an OVER clause (and that clause must have an ORDER BY clause).

To fix this issue, be sure to include an OVER clause when calling the CUME_DIST() function.

Example of Error

Here’s an example of code that produces the error:

SELECT
    VendorId,
    ProductName,
    ProductPrice,
    CUME_DIST( ) AS CUME_DIST
FROM Products;

Result:

Msg 10753, Level 15, State 3, Line 5
The function 'CUME_DIST' must have an OVER clause.

Here I forgot to include the OVER clause when calling the CUME_DIST() function. This resulted in an error.

Solution

The error message pretty much tells us how to fix the error. We must include an OVER clause when calling the CUME_DIST() function:

SELECT
    VendorId,
    ProductName,
    ProductPrice,
    CUME_DIST( ) OVER ( ORDER BY ProductPrice ) AS CUME_DIST
FROM Products;

Result:

VendorId  ProductName                      ProductPrice  CUME_DIST
--------  -------------------------------  ------------  ---------
1004      Bottomless Coffee Mugs (4 Pack)  9.99          0.1      
1003      Hammock                          10            0.2      
1001      Long Weight (green)              11.99         0.3      
1004      Tea Pot                          12.45         0.4      
1001      Long Weight (blue)               14.75         0.5      
1001      Left handed screwdriver          25.99         0.7      
1001      Right handed screwdriver         25.99         0.7      
1002      Sledge Hammer                    33.49         0.8      
1003      Straw Dog Box                    55.99         0.9      
1003      Chainsaw                         245           1        

This time it worked without error.

Note that the OVER clause needs to have an ORDER BY clause, otherwise we’ll get a different error.