Hi,

The following query gives an error in MySql:

SELECT SUM((SELECT SUM(a.id) FROM users AS b)) FROM users AS a

Error: #1111 - Invalid use of group function


I know the query doesn't really make sense and it is easy to rewrite it,
but the error occurred in some generated sql (this is obviously just a
stripped version of it) and I want to know what the reason for the error
is, so I can change the sql generator.

I already tried several fixes, and surprisingly, the following worked:

SELECT SUM((SELECT SUM(a.id*IF(b.id=0,1,1)) FROM users AS b)) FROM users AS a

To me, it seems that it fails, because the expression within the SUM part
is independant of the query in which it aggregates, or something like that.
Can anyone explain / clarify this and point me to where this restriction
is mentioned in the mysql documentation?



Thanks,
Erik-Jan Krijgsman