Sum some values of a table

[cosmicyes]

I posted a similar question just a few days ago and made some advantages with your help.
Unfortunately I did not solve the problem completely.

I have loaded a table from a csv (sample data):

I have loaded every line - not using "group by" as I try to solve it in the frontend.

* Counting the number of distinct claims is easy: count(distinct claim).
* Also to get the total of all costs is solvable:  sum(cost).

Question:
How can I get the sum of all pieces of each distinct claim?
The correct result is 13

What I need is something like sum(pieces for distinct claim)
Of course I know this syntax won´t work 😉

***
another approach is to solve it in the data model by using "group by"

What I have tried:

claim_tmp:
LOAD 
claim,
pieces,
costtype,
cost,
name
FROM mydata.qvd;

claim:
LOAD
claim_tmp.claim as claim,
MAX(claim_tmp.pieces) as pieces // could have used MIN also...
sum(claim_tmp.cost) as cost,
// how to load costtype here?
// how to load name here?
RESIDENT claim_tmp
GROUP BY claim_tmp.claim;

The problem is that I want to display all the data in a straight table in the frontend.
Using the MAX-function seems quite clever to me 😉
But how to get costtype and name as I am only allowed to use Aggragation functions when using GROUP BY?

The last problem is to get the sums of all costtypes.
The result should be:
Type 1: 240
Type 2: 200

As you can see I have tried a lot before asking - but I am still an newbe.

Thank you in advance for any hints!

Rainer

[Lisa_P · Accepted solution]

Try this:

sum(aggr(sum(distinct pieces), claim))

[cosmicyes]

Hi Lisa,

thank you!
I have heard about the aggr-function but have never used it.
This seems to be a very mighty tool and I think this helps me a lot - though my head explodes when I read the docu 😉
I´ll play arround with this!