Help with multiple column Aggregate Sum and counting unique names

Ask the community
, ,
1

I created a form /database that collects a member’s full name, email, member ID, date/time of the volunteering, and volunteer hours completed from our site members. On the form, there are 38 different categories (database fields) where the members can place their hours. For the group leads, I have a summary page with text elements that needs to show the total hours for each category, the total # of volunteers (making sure not to count duplicate names), the overall total of hours (basically adding up all of the 38 fields), and I need to be able to have a drop down list of available years that will filter out the hours and volunteer totals specific to that year.

Thanks to some direction provided by AnthonyB to look into aggregate data functions, I was able to come up with the working code seen below (I took out some repetitive parts to help shorten the length for this example) to help with adding up each individual category and listing it on the page. So now, I am stuck at the next part so I am looking for more help to figure out how to:

Issue 1 - Using the aggregate functions to sum up multiple columns of data. I have tried combining the sums in a couple of ways but none seem to work right. So far, I came across only 1 other person in the forums asking about this same thing but they had no responses.

Issue 2 - Counting the Member ID field (“memID”) to see what the total number of volunteers are while ignoring duplicates since there can be more than 1 volunteer entry for each member.

I also have a 3rd issue but will move it to a separate post so this does not get overly complicated.

Any help is truly appreciated. :slight_smile:

import wixData from 'wix-data';

$w.onReady(() => {
    Sum_hours();
});

export function Sum_hours() {
    wixData.aggregate("Volunteer_Entries")

 //Community Activities Sum
        .sum("communityAthlHrs", "sum14")
        .sum("communityCoatKidsHrs", "sum15")
        .sum("communityColuSquiHrs", "sum16")
        .sum("communityDisaHrs", "sum17")
        .sum("communityEldeWidoHrs", "sum18")
        .sum("communityGlobWheeHrs", "sum19")
        .sum("communityHabiHumaHrs", "sum20")
        .sum("communityHospHealOrgHrs", "sum21")
        .sum("communityPhysIntDisaHrs", "sum22")
        .sum("communitySchoEducHrs", "sum23")
        .sum("communityScoutYoutHrs", "sum24")
        .sum("communityVeteMiliHrs", "sum25")
        .sum("communityYoutWelfHrs", "sum26")
        .sum("communityMiscHrs", "sum27")

 //Other Commitments Sum
        .sum("otherVisiSick", "sum35")
        .sum("otherVisiBere", "sum36")
        .sum("otherNumBlooDona", "sum37")
        .sum("otherServSickDisaHrs", "sum38")
 
        .run()
        .then((results) => {

 //Community Activities Display
            $w('#text252').text = results.items[0].sum14.toString();
            $w('#text254').text = results.items[0].sum15.toString();
            $w('#text256').text = results.items[0].sum16.toString();
            $w('#text258').text = results.items[0].sum17.toString();
            $w('#text260').text = results.items[0].sum18.toString();
            $w('#text262').text = results.items[0].sum19.toString();
            $w('#text264').text = results.items[0].sum20.toString();
            $w('#text266').text = results.items[0].sum21.toString();
            $w('#text268').text = results.items[0].sum22.toString();
            $w('#text270').text = results.items[0].sum23.toString();
            $w('#text272').text = results.items[0].sum24.toString();
            $w('#text274').text = results.items[0].sum25.toString();
            $w('#text276').text = results.items[0].sum26.toString();
            $w('#text278').text = results.items[0].sum27.toString();

 //Other Commitments Display
            $w('#text294').text = results.items[0].sum35.toString();
            $w('#text296').text = results.items[0].sum36.toString();
            $w('#text298').text = results.items[0].sum37.toString();
            $w('#text300').text = results.items[0].sum38.toString();
 //Total Hours Display
            $w('#text305').text = results.items[0].sumTotal.toString();
        });
}
2

Hi Chad,

I have a little time to post regarding #2 since I was wanting a similar result recently in something that I was working on. This uses an aggregate query grouping by MemID and utilizes the count function. The idea is that grouping by MemID will get all of the unique MemIDs (volunteers). The length of the returned array is the volunteer count. Looking at the results in the console will give you a better idea of what happens when you group by a field and use the count function on it.

export function MemberCount(){
  wixData.aggregate("Volunteer_Entries")
    .group("memID")
    .count()
    .ascending("_id")
    .run()
    .then((results) => {
        console.log(results);
        let volunteerCount = results.length;
    })
}
3

Oh that makes sense for the 2nd issue. I will try that out here soon. As for my 1st issue, I have come up with a usable solution. I ended up creating a data hook after Query function that summed up all of the categories once a form was submitted and added the total to its own column within the database collection. Then I will use the same aggregate sum functions as above to sum up that specific column. I figured it would at least make it easier for other things to retrieve those totals if needed down the road. Now I just need to figure out the year filter portion of the issue.