If you’re getting error message 4112 that reads “The function ‘CUME_DIST’ must have an OVER clause with ORDER BY” when using the CUME_DIST() function, it’s probably because you’re omitting the ORDER BY clause from the OVER clause.

When using the CUME_DIST() function in SQL Server, we must include an OVER clause that contains an ORDER BY clause. This error happens when we provide the OVER clause but not the ORDER BY clause.

To fix this error, add an ORDER BY clause to the OVER clause.

Example of Error

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

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

Result:

Msg 4112, Level 15, State 1, Line 5
The function 'CUME_DIST' must have an OVER clause with ORDER BY.

This error occurred because the OVER clause doesn’t have an ORDER BY clause.

Simply putting an ORDER BY clause at the end of the query is not sufficient:

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

Result:

Msg 4112, Level 15, State 1, Line 5
The function 'CUME_DIST' must have an OVER clause with ORDER BY.

Although there’s nothing wrong with having an ORDER BY clause at the end of the query, we still need to have one in the OVER clause.

Solution

To fix this issue, simply add an ORDER BY clause to the OVER clause:

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

Result:

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

This time the function worked as expected, without error.

The above examples also use a PARTITION BY clause to partition the result set, but this is optional. We can omit the PARTITION BY clause but we can’t omit the ORDER BY clause.