How to fix Error 1015 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 1015 — Aggregate in ON Clause

Why This Error Happens

This error occurs because Transact-SQL does not permit aggregate functions (such as SUM(), COUNT(), AVG(), MAX(), or MIN()) to be evaluated directly within the ON clause of a JOIN statement. The SQL Server query optimizer processes JOIN conditions before the GROUP BY or aggregation logic is applied, meaning the aggregate values simply do not exist yet at the point the engine tries to evaluate the join logic. This is typically a query construction issue rather than a server configuration or permissions problem, often introduced after a migration to SQL Server 2025 where legacy queries with loose syntax are now being strictly parsed.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.
  • Check the Activity Monitor or recent query history to locate the specific script throwing Error 1015.
  • Look specifically for syntax where an aggregate function appears immediately after ON or inside an AND/OR condition within the JOIN clause.

Step 2: Refactor the Query Using a Subquery or CTE

  • Move the aggregate logic out of the JOIN...ON clause and into a Common Table Expression (CTE) or a derived table (subquery).
  • Perform the aggregation in the subquery first, then join the result of that subquery to your main table.
  • Example structure: SELECT * FROM TableA JOIN (SELECT ID, SUM(Value) as Total FROM TableB GROUP BY ID) AS SubQuery ON TableA.ID = SubQuery.ID

Step 3: Move Aggregate Logic to the HAVING Clause

  • If the goal was to filter grouped results rather than join on them, remove the aggregate from the ON clause entirely.
  • Ensure the query includes a GROUP BY clause for the non-aggregated columns.
  • Place the aggregate condition inside a HAVING clause instead, such as HAVING SUM(Value) > 100.

Step 4: Validate Compatibility Level Settings

  • Open a new query window and run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDBName'; to check the current database compatibility level.
  • If the database was recently upgraded, ensure the compatibility level matches SQL Server 2025 standards (170) to ensure the parser handles the syntax using the newest rule set.
  • Run ALTER DATABASE YourDBName SET COMPATIBILITY_LEVEL = 170; if an update is required.

Alternative or Verification Steps

  • Verification: Execute the modified query in a test environment using SET SHOWPLAN_TEXT ON; to ensure the execution plan compiles successfully without throwing Error 1015 before deploying to production.
  • Alternative: If refactoring is complex, utilize a temporary table (#TempTable) to store the aggregated results first, then perform a standard JOIN against the temporary table in a separate, subsequent step.