If you’re getting error number 4123 that reads “Window element in OVER clause can not also be specified in WINDOW clause” in SQL Server, it’s probably because you’re referring to a named window with the same clause that’s in the named window.

When we use the OVER clause to refer to a named window in SQL Server, we can’t include a clause that’s also present in the named window.

To fix the issue, remove one of the clauses – either the one in the OVER clause or the one in the named window.

Example of Error

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

SELECT 
    District, 
    Name, 
    Population,
    SUM(Population) OVER(win PARTITION BY District) AS "Population"
FROM City 
WHERE CountryCode = 'AUS' 
WINDOW win AS (PARTITION BY District);

Result:

Msg 4123, Level 15, State 2, Server bd79c358ea10, Line 69
Window element in OVER clause can not also be specified in WINDOW clause.

In this case, I used a PARTITION BY clause in both the OVER clause and the WINDOW clause (which defines the named window).

This is not allowed and so I got the error.

Solution

All I need to do to fix the problem is remove one of the PARTITION BY clauses:

SELECT 
    District, 
    Name, 
    Population,
    SUM(Population) OVER win AS "Population"
FROM City 
WHERE CountryCode = 'AUS' 
WINDOW win AS (PARTITION BY District);

Result:

District             Name            Population  Population 
-------------------- --------------- ----------- -----------
Capital Region       Canberra             322723      322723
New South Wales      Sydney              3276207     3993949
New South Wales      Newcastle            270324     3993949
New South Wales      Central Coast        227657     3993949
New South Wales      Wollongong           219761     3993949
Queensland           Brisbane            1291117     1805236
Queensland           Gold Coast           311932     1805236
Queensland           Townsville           109914     1805236
Queensland           Cairns                92273     1805236
South Australia      Adelaide             978100      978100
Tasmania             Hobart               126118      126118
Victoria             Geelong              125382     2990711
Victoria             Melbourne           2865329     2990711
West Australia       Perth               1096829     1096829

In this case I removed the PARTITION BY clause from the OVER clause in the SELECT list.

Note that I also removed the parentheses. If I hadn’t done this, I would’ve seen a different error.

An alternative fix would have been to remove the PARTITION BY clause from the WINDOW clause (while keeping the one in the OVER clause):

SELECT 
    District, 
    Name, 
    Population,
    SUM(Population) OVER(win PARTITION BY District) AS "Population"
FROM City 
WHERE CountryCode = 'AUS' 
WINDOW win AS ();

Mind you, in this case I’m removing all clauses from the WINDOW function, which kind of defeats the purpose of even having a named window.