SUM during load

[#1]

Hello,

I'm a bit slow. Can somebody help me on summing during load.

ODBC CONNECT TO minitrac (XUserId is KBSeeUROeB, XPassword is GdfaIWFNGDdeWbFGUDbB);
GM:
LOAD `GL_Account`,
 TranDate,

 TranNo,
 `Control_No`,
 HistUpd,
 xglacctno,
 xtrandate,
 REGION,
 MAKE,
 MODEL,
 purgechar (SERIAL,'GM') as SERIAL,
 DIVISION,
 `HR_METER`,
 `LAST_HR_METER`,
 REV,
 REP,
 CUSTOMER,
 `GM_EXP`,
 `GM_START`,
 BRANCH,
 `UNIT_OPTIONS`,
 `GM_PAYMENT`,
 `LEASE_PAYMENT`,
 CAHRS,
 `LEASE_RESIDUAL`,
 `LEASE_EXPDATE`,
 `OT_RATE`,
 `OT_INTERVAL`,
 `CONTRACT_GP`',
 SUM(REV) as TOTAL_REV;

SQL SELECT *
FROM minitrac.mpglcn where Year(TranDate) <> "2030";

I get an error when the SUM(REV) as TOTAL_REV is in the load script. Am I doing something wrong? I did a lot of searches, and I think I have to calculate the SUM(REV) on another load statement. However, I'm not a SQL person by any means.

Any help is much appreciated!

[johnw]

To do a sum() or other aggregation expression in the script, you need a GROUP BY. So you'll need to group by that whole list of fields in the load statement. As an alternative, it might be more efficient to do the group by in the SQL rather than in the LOAD.

LOAD `GL_Account`,
TranDate,
...
TOTAL_REV
;
SQL SELECT
<all fields but REV>
,sum(REV) as TOTAL_REV
FROM minitrac.mpglcn
WHERE year(TranDate) <> "2030"
GROUP BY
<all fields but REV>
;