How do you sum or aggregate data from collections into a table?

Ask the community
,
1

I have a collection of results for multiple events, and I would like to be able to total multiple columns from the collection for each participant and have those results display in a table. I have been searching the forum and reading through old threads, and it appears that most people are totaling their values through the use of a for each loop. Most of these threads were created before May 2018 so I wasn’t sure how long the WixDataAggregateResult (https://www.wix.com/code/reference/wix-data.WixDataAggregateResult.html) function has been around, but will that accomplish what I need? The examples given only appear to sum one column and I would like to sum multiple. Is there a different newer function that will accomplish what I need? Also, a lot of these examples are displaying the data into textboxes. How would I go about displaying them into a table?

This is the code I have so far. What it does is get the current year of today’s date and display it into a textbox at the top of the page to signify the year it is showing statistics for (defaulting to this year on load), reaches out to another collection ‘Tournament’ to get an array of all the tournaments for the current year, and then pass the array of tournaments to an aggregate on the ‘TxResults’ Collection, grouped by the ‘member’ field and summing multiple columns in the collection.

import wixData from ‘wix-data’;

$w.onReady( function ()
{
// Gets today’s date
const today = new Date();
// Get the current year
const year = Number(today.getFullYear());
//console.log(year)
// set the year on the text
$w(‘#yeartext’).text = String(year);
//get list of txs for the current season not including classic
wixData.query(‘Tournaments’)
.eq(‘season’, year)
.ne(‘classic’, true )
.ascending(‘date’)
.find()
.then((results) => {
let txdata = results.items;
//get names of tournaments for current season
let txlist = txdata.map(item => item.title);
//console.log(txlist)

     const  filter = wixData.filter(txlist); 
     //const having = wixData.filter().gt("maxPopulation", 1000000); 

     wixData.aggregate("TxResults") 
            .filter(filter) 
            .group("member") 
            .sum('caught','alive','totallbs','totaloz','totalpts') 
             //.having(having) 
            .descending("totalpts") 
            .run() 
            .then( (tresults) => { 
                let  items = tresults.items; 
                let  numItems = tresults.length; 
                let  hasNext = tresults.hasNext(); 
                console.log(numItems) 
                console.log(hasNext) 
                console.log(items) 
                $w('#dataset1').setFilter(wixData.filter(items)); 
            } ) 
            . catch ( (error) => {

let errorMsg = error.message;
let code = error.code;
} );
})
. catch ((err) => {
let errorMsg = err;
})
});

1 Like
2

Can anyone please help with this?

3

Bump. Anyone please? I can’t continue any further progress on my site without it

4

Bump

5

Bump. Nobody has any examples of using aggregation or summing multiple collection fields and displaying them into a table?

6

No replies at all??

7

Hey there, I’m after the same thing myself, did you manage to get it sorted?

8

Almost. If I use the below code, and then console.log the items variable, the output looks good. However, I cannot seem to get it into the table. Hopefully this helps you out some. If you have any suggestions, I can try them on my end as well.

import wixData from ‘wix-data’;

$w.onReady( function ()
{
$w(“#table1”).columns = [
{
“id”: “col1”,
“dataPath”: “member”,
“label”: “Member”,
“type”: “string”
},
{
“id”: “col2”,
“dataPath”: “caught”,
“label”: “Caught”,
“type”: “number”
},
{
“id”: “col3”,
“dataPath”: “alive”,
“label”: “Alive”,
“type”: “number”
},
{
“id”: “col4”,
“dataPath”: “totalLBs”,
“label”: “Total LBs”,
“type”: “number”
},
{
“id”: “col5”,
“dataPath”: “totalOz”,
“label”: “Total Oz”,
“type”: “number”
},
{
“id”: “col6”,
“dataPath”: “totalpts”,
“label”: “Total Points”,
“visible”: true ,
“type”: “number”
}
];

const filter = wixData.filter().startsWith(“title”, “18-”);

wixData.aggregate("TxResults") 
    .filter(filter) 
    .sum('totalpts','Total Pts') 
    .sum('caught','Caught') 
    .sum('alive','Alive') 
    .sum('totalLBs','Total LBs') 
    .sum('totalOz','Total Oz') 
    .group("member") 
    .descending("totalpts") 
    .run() 
    .then((results) => {

let items = results.items;
let numItems = results.length;
let hasNext = results.hasNext();
console.log(numItems)
console.log(hasNext)
console.log(items)
$w(“#table1”).rows = items;
const members = results.items.map(x => x.member)
.filter((obj, index, self) => index === self.indexOf(obj));
const aggregated = members.map(x => {
return {
member: x,
totalpts: results.items.filter(obj => obj.member === x)
.map(z => z.totalpts)
.reduce((sum, current) => sum + current)
}})

    console.log(aggregated) 
    $w("#table1").rows = aggregated;

})});

9

Thanks a million, I’ll give it a whirl and keep you posted if I figure out the table bit

10

@jobbojnr Any luck yet?

11

@poolshark314 I have been trying to write a code which would add the number of engagements per each month.
I have a database which collects the date and month and the number of people engaged with for every new entry. I have tried to adjust the code you’ve provided here but all that is happening is that a table appears on the page which has two separate columns which show month and total of engagements but the addition of all the engagements does not happen. So the table only throws up ‘may 25’ and ‘may 16’ instead of adding them up.
Any ideas on what I’m doing wrong?

import wixData from ‘wix-data’;

$w.onReady( function () {
$w(“#table1”).columns = [
{
“id”: “col1”,
“dataPath”: “month”,
“label”: “Month”,
“type”: “string”
},
{
“id”: “col2”,
“dataPath”: “numberOfAtendees”,
“label”: “Total engagements”,
“visible”: true ,
“type”: “number”
}
];

wixData.aggregate("engagements") 
    .sum('numberOfAtendees') 
    .group("month") 
    .run() 
    .then((results) => {

let items = results.items;
let numItems = results.length;
let hasNext = results.hasNext();
console.log(numItems)
console.log(hasNext)
console.log(items)
$w(“#table1”).rows = items;
const month = results.items.map(x => x.month)
.filter((obj, index, self) => index === self.indexOf(obj));
const aggregated = month.map(x => {
return {
month: x,
numberOfAttendees: results.items.filter(obj => obj.month === x)
.map(z => z.numberOfAttendees)
.reduce((sum, current) => sum + current)
}})

    console.log(aggregated) 
    $w("#table1").rows = aggregated;

})});

12

@lenkamurova Unfortunately I haven’t been able to get it to work either. It seems like no one can so far.

13

I am also looking to do this with no luck. I want to aggregate totals of two fields (hours and miles) for the logged in member. I’m collecting the transactions for hours and miles in a data collection filtered by member (owner is the filter when I display the records) but can’t summarize the rows to show the member total hours and miles.

I can’t figure out if this has been submitted as a feature request for Wix tables. That would simplify so much!

I have learned bits of Wix code from the forum but am not a programmer. This is what I’ve entered but to no avail:

import wixUsers from ‘wix-users’;
import wixData from ‘wix-data’;

$w.onReady( function () {

//Get loginEmail for owner (who updated the MilesHoursLog)
let user = wixUsers.currentUser;
let userId = user.id;
let isLoggedIn = user.loggedIn;
let userRole = user.role;
user.getEmail()
.then((email) => {
let userEmail = email; // “user@something.com”
$w(‘#input3’).value = userEmail; //enter loginEmail into input field so it will go in Totals table when input fields are submitted
// }
// );

        wixData.query("MilesHoursLog") 
            .limit(1000) // include a limit if you have more than 50 items

//match to owner loginEmail
.hasSome(“loginEmail”, “userEmail”)
.find()
.then((result) => {
console.log(result.items);
//aggregate hours for all rows. In MilesHoursLog, the field to aggregate is hours; in Totals data collection, the field is totalHours
const totalHours = result.items.map(x => x.totalHours)
.filter((obj, index, self) => index === self.indexOf(obj))
const aggregated = totalHours.map(x => {
return {
hours: x,
total: result.items.filter(obj => obj.hours === x)
.map(z => z.totalHours)
.reduce((sum, current) => sum + current)
}
});
//put total hours into input field for display purposes (update upon submit)
$w(‘#input1’).value = totalHours; //Why isn’t this field loading into the input field? Until it does, I can’t submit the input fields to the Totals data collection
});
});

wixData.query("MilesHoursLog") 
    .limit(100) // include a limit if you have more than 50 items

//.ascending(“date”)
.hasSome(“loginEmail”, “userEmail”)
.find()
.then((result) => {
console.log(result.items);
//aggregate miles for all rows. In MilesHoursLog, the field to aggregate is miles; in Totals data collection, the field is totalMiles (I couldn’t figure out how to do both totals in first query)
const totalMiles = result.items.map(x => x.totalMiles)
.filter((obj, index, self) => index === self.indexOf(obj))
const aggregated = totalMiles.map(x => {
return {
miles: x,
total: result.items.filter(obj => obj.miles === x)
.map(z => z.totalMiles)
.reduce((sum, current) => sum + current)
}
});
//put total miles into input field for display purposes (update on submit)
$w(‘#input2’).value = totalMiles; //Why isn’t this field loading into the input field? Until it does, I can’t submit the input fields to the Totals data collection
});
});

Any help will be gratefully accepted.

14

neeerdd bump

15

I finally figured this out after several attempts! (above example signed in as Llaa Sjma). Final WORKING code!

import wixData from ‘wix-data’;
import wixUsers from ‘wix-users’;

let user = wixUsers.currentUser;

let userId = user.id;
let isLoggedIn = user.loggedIn;

user.getEmail()
.then( (email) => {
let userEmail = email;
} );

$w.onReady(() => {
Sum_amount();
});
let having = wixData.filter().eq(“_owner”,userId)

export function Sum_amount(){
wixData.aggregate(“MilesHoursLog”)
.group(“_owner”)
.having(having)
.sum(“hours”,“sumHours”)
.sum(“miles”,“sumMiles”)
.run()
.then( (results) => {
$w(‘#input1’).value = results.items[0].sumHours; //display total in input field at bottom of table
$w(‘#input2’).value = results.items[0].sumMiles; //display total in input field at bottom of table
});
}

I ended up using group and having instead of filter (highlighted in red above). Simple once I tried it based on another post. I had wanted to filter the dataset by logged in user before aggregating. (Seems like it should be super easy, similar to being able to filter the dataset on the page.)

I hope this helps some other poor soul stumbling through Wix without coding experience. I am SO excited because we are collecting volunteer hours and miles by member and they can now see their own totals after they have entered them.

16

Thanks for doing this Barb! I am having trouble interpreting the code with the inputs and Users portion. Could you please assist with the code block I have at the top?

17

@poolshark314 I’m a newbie and I didn’t understand your code. I found my method by reading several other posts and trial and error. Sorry I can’t help with your code.