Sum & group by in load from resident table

[flygstolen_fred]

Hi,

ProductCode	ProductDescription	ProductPrice	ProductUnits	ProductCurrency
AVB	Insurance for Karl	150	1	SEK
AVB	Insurance for Hanna	75	1	SEK
KON	Bicycle	500	2	SEK

I have a Product table with data that are loaded to my app, but now I wanna use the data in the load script for other calculations and need to know how many ProductUnits I have for every ProductCode. I'm trying to something like this but only get invalid expression:

Any help are appreciated

TMPPRODUCT:

NOCONCATENATE

LOAD SUM(ProductUnits) as ProductUnits, ProductCode, ProductDescription, ProductPrice, ProductCurrency RESIDENT Product WHERE MATCH(ProductCode, 'AVB', 'KON', 'KRED') GROUP BY ProductCode;

[Gysbert_Wassenaar · Accepted solution]

You must put every field not used in an aggregation function (like sum) into the group by clause.

TMPPRODUCT:

NOCONCATENATE

LOAD SUM(ProductUnits) as ProductUnits, ProductCode, ProductDescription, ProductPrice, ProductCurrency RESIDENT Product WHERE MATCH(ProductCode, 'AVB', 'KON', 'KRED') GROUP BY ProductCode, ProductDescription, ProductPrice, ProductCurrency ;

[prashanth1712]

Hi ,

As Gysbert said you have to include every column in your group by statement.

TMPPRODUCT:

NOCONCATENATE

LOAD SUM(ProductUnits) as ProductUnits,

ProductCode,

ProductDescription,

ProductPrice,

ProductCurrency

RESIDENT Product WHERE MATCH(ProductCode, 'AVB', 'KON', 'KRED')

GROUP BY ProductCode,

ProductDescription,

ProductPrice,

ProductCurrency;

Regards

Prashanth

[rammuthiah]

Use all fields,which is used in load except ProductUnits. i.e..

TMPPRODUCT:

NOCONCATENATE

LOAD

ProductCode,

ProductDescription,

ProductPrice,

ProductCurrency,

SUM(ProductUnits) as ProductUnits

RESIDENT Product WHERE MATCH(ProductCode, 'AVB', 'KON', 'KRED')

GROUP BY

ProductCode,

ProductDescription,

ProductPrice,

ProductCurrency;