EC
3168
MySQLERRORNotableWindow FunctionsHIGH confidence

Window function cannot be used with this aggregation

Production Risk

Low — syntax error; restructure the query.

What this means

A window function is being used in a context where it conflicts with GROUP BY aggregation, such as mixing grouped aggregates and window functions at the same query level without proper wrapping.

Why it happens
  1. 1Mixing window functions and GROUP BY aggregates in the same SELECT without using a subquery or CTE to separate the aggregation levels.
How to reproduce
trigger — this will error
trigger — this will error
SELECT dept, SUM(sal) OVER (), SUM(sal) FROM t1 GROUP BY dept;

expected output

ERROR 3168 (HY000): Window function used with aggregation.

Fix

Separate the aggregation into a derived table

Separate the aggregation into a derived table
SELECT dept, dept_sum, SUM(dept_sum) OVER () AS total_sum
FROM (SELECT dept, SUM(sal) AS dept_sum FROM t1 GROUP BY dept) sub;

Why this works

Aggregation happens in the inner query; window functions operate on the aggregated result.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 3168 ER_WINDOW_INVALID_WINDOW_FUNC_USE

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors