Lets supose I have a table with every entry in the system:

CREATE CURSOR cMovs (iid int autoinc, iAgentID int, yAmount y, dDate d)

RAND(-1)
FOR lnCounter=1 TO 100
  INSERT INTO cMovs (iAgentID, yAmount, dDate) ;
     VALUES ((RAND()*10)-1, RAND()*1000000,date(2005,01,01)+ (RAND()*45))
ENDFOR

And I have an entity where I would like to get the sumary of each agent by month and year...

CREATE CURSOR cAgent (iid int autoinc, yMonth y, nMonth int,  nYear int)
FOR lnCounter=1 TO 10
  INSERT INTO cAgent (yMonth, nMonth, nYear) ;
   VALUES (0,1,2005)
ENDFOR

I'd like to Update the cAgent cursor with a single UPDATE-SQL...

UPDATE cAgent ;
  SET yMonth = (SELECT SUM(yAmount) ;
                  FROM cMovs ;
                  WHERE cMovs.iAgentID = cAgent.iID 
                    AND MONTH(dDate)= cAgent.nMonth ;
                    AND YEAR(dDate) = cAgent.nYear) ;
  WHERE cAgent.nMonth = 1 AND cAgent.Year=2005

If I do the above UPDATE-SQL I get the Error 1828 "SQL: Illegal GROUP BY in subquery.".

How should I create that sentece?, I'd like to avoid the use of SCAN ... ENDSCAN to get an literal cmovs.iAgentID = ScannedTable.IID.

Is it possible?, or is this a of ANSI-SQL-rule issue?. I could think that that's possible!

Update:The title should day "SubQuery" not Correlated

TIA