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! 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!