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; }) });
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.
neeerdd bump
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.
Thanks a million, I’ll give it a whirl and keep you posted if I figure out the table bit
Hey there, I’m after the same thing myself, did you manage to get it sorted?
No replies at all??
Bump. Nobody has any examples of using aggregation or summing multiple collection fields and displaying them into a table?
Bump
Bump. Anyone please? I can't continue any further progress on my site without it
Can anyone please help with this?