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)) ENDFORAnd 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) ENDFORI'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=2005If I do the above UPDATE-SQL I get the Error 1828 "SQL: Illegal GROUP BY in subquery.".