>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

summary

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

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

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

CLEAR

* this is the best
UPDATE A ;
  SET yMonth = cMovs.yAmount ;
  FROM (SELECT iAgentID, SUM(yAmount) yAmount ;
                  FROM cMovs ;
                  WHERE MONTH(dDate)=1 AND YEAR(dDate)=2005;
                  group by 1) cMovs;
  		JOIN cAgent A;
        ON  cMovs.iAgentID = A.iID;
 		WHERE  A.nMonth = 1 AND A.nYear=2005
BROWSE

* this is 90% but more readable
* you can correlate 1 field only
UPDATE A ;
  SET yMonth = (SELECT SUM(yAmount) FROM cMovs WHERE cMovs.iAgentID = A.iID AND MONTH(dDate)=1 AND YEAR(dDate)=2005);
  FROM cAgent A;
  WHERE A.nMonth = 1 AND A.nYear=2005
 
BROWSE

* not efficient but flexible

 UPDATE A ;
  SET yMonth = cMovs.yAmount ;
  FROM (SELECT iAgentID, SUM(yAmount) yAmount ;
                  FROM cAgent JOIN cMovs  ;
                  ON cMovs.iAgentID = cAgent.iID ;
                    AND MONTH(dDate)= cAgent.nMonth ;
                    AND YEAR(dDate) = cAgent.nYear;
                    WHERE cAgent.nMonth = 1 AND cAgent.nYear=2005 ;
                  group by iAgentID) cMovs;
  		JOIN cAgent A;
        ON  cMovs.iAgentID = A.iID

BROWSE

* the worse

UPDATE A ;
  SET yMonth = cMovs.yAmount ;
  FROM cAgent A;
    JOIN (SELECT iAgentID , MONTH(dDate) nMonth , YEAR(dDate) nYear , SUM(yAmount) yAmount ;
                  FROM cMovs group by 1,2,3) cMovs;
         ON  cMovs.iAgentID = A.iID;
         AND cMovs.nMonth   = A.nMonth ;
         AND cMovs.nYear    = A.nYear;
  WHERE A.nMonth = 1 AND A.nYear=2005

BROWSE