

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sum & group by in load from resident table
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;
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ;
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ;
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
