Resident and Group by

[#1]

Hello!

I get a invalid expression error on this scipt when trying to use sum and group by and its driving me insane. Any help?

VAtmp:

LOAD

     PART_NO,
     CONTRACT,
     ISSUE_QTY,
     Month
FROM
Z:\xxxx\xxxx\Extracted\Sup.QVD
(qvd);

VA:

LOAD

    PART_NO,
     CONTRACT,
     sum(ISSUE_QTY) as sumQTY,
     Month
    
Resident VAtmp Group by PART_NO;

[swuehl · Accepted solution]

You need to either use an aggregation function around CONTRACT (could be something like concat(CONTRACT) as concatCONTRACT, only(CONTRACT) as CONTRACT or count(CONTRACT) as countCONTRACT or whatever appropriate)

or add CONTRACT to your group by fields (...group by PART_NO, CONTRACT; )

[daveamz]

Hello,

     You should group by all non-calculated fields: 

VA:

LOAD

    PART_NO,
     CONTRACT,
     sum(ISSUE_QTY) as sumQTY,
     Month

Resident VAtmp Group by PART_NO, CONTRACT, Month;