couchbase query returning high value on sum

Question

My document contains 183 of type Person and 2723 documents of type Purchase. Basically, a person buys products. Example documents are as follows. A person might never have purchased anything.

{"Type" : "Person", PersonID: 1, InitialFee: 100, "MemberGroupTypeID" : 1, _id:"Person_1_1"},
{"Type" : "Person", PersonID: 2, InitialFee: 200, "MemberGroupTypeID" : 1, _id:"Person_1_1"},
{"Type" : "Purchase", PersonID: 1, PurchaseID:1, Value: 5, "MemberGroupTypeID" : 1, _id:"Purchase_1_1_1"},
{"Type" : "Purchase", PersonID: 1, PurchaseID:2, Value: 10, "MemberGroupTypeID" : 1, _id:"Purchase_2_1_1"},
{"Type" : "Purchase", PersonID: 1, PurchaseID:3, Value: 25, "MemberGroupTypeID" : 1, _id:"Purchase_3_1_1"}

Basically, the output expected is:

MemberGroupTypeID: 1, FeesTotal: 300, TotalPurchasesCost: 40

I have the following query which returns correct total purchase cost for each type of members group but the FeesTotal is way too high and I don't know how it is getting that number by all means. I mean, I couldn't spot a relationship between the expected number and the actual FeesTotal I am getting after running the query:

SELECT SUM(person.InitialFee) AS FeesTotal,
SUM(purchase.Value) as TotalPurchasesCost,
person.MemberGroupTypeID
FROM bucket person
LEFT JOIN bucket purchase
ON person.MemberGroupTypeID= purchase.MemberGroupTypeID
WHERE person.Type = "Person" AND purchase.Type = "Purchase" AND
person.PersonId = purchase.PersonId
GROUP BY person.MemberGroupTypeID;

Answer 1

When JOIN each Left Side can produce 1:n. If you aggregate then it counted multiple times.

Use two level aggregation by grouping whole left side document first then group the desired fields.

SELECT b.MemberGroupTypeID,
       SUM(b.InitialFee) AS FeesTotal,
       SUM(b.PurchasesCost) AS TotalPurchasesCost,
FROM (SELECT person.InitialFee,
             person.MemberGroupTypeID,
             SUM(purchase.`Value`) AS PurchasesCost
       FROM bucket AS person
       LEFT JOIN bucket AS purchase
       ON person.MemberGroupTypeID = purchase.MemberGroupTypeID
          AND purchase.Type = "Purchase" AND person.PersonId = purchase.PersonId
       WHERE person.Type = "Person"
       GROUP BY person) AS b
GROUP BY b.MemberGroupTypeID;