3642
MySQLERRORNotableQueryHIGH confidence
Aggregate function not allowed in window function or lateral derived table
Production Risk
Medium — Query fails; requires restructuring.
How to reproduce
trigger — this will error
trigger — this will error
SELECT SUM(SUM(val)) OVER (PARTITION BY grp) FROM t GROUP BY grp;
expected output
ERROR 3642 (HY000): Aggregate function not permitted in window function or lateral derived table.
Fix
Pre-aggregate in a CTE
Pre-aggregate in a CTE
WITH agg AS (SELECT grp, SUM(val) AS s FROM t GROUP BY grp) SELECT SUM(s) OVER (PARTITION BY grp) FROM agg;
Why this works
Moves the inner aggregate to a CTE, providing a clean reference for the window function.
What not to do
✕
Version notes
Sources
Official documentation ↗
MySQL 8.0 — 3642 ER_AGGREGATE_FUNCTION_IN_WINDOW_OR_LATERAL
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev