Hi everyone,
As you can see on the picture I have a column in my dataset collection with numbers. I would like to be able to sum up all these numbers to then be able to display the total on my site in a text box.
I have been trying to figure this out for 2 days nows, reading everything I could find. Still can’t figure out how to do it. Hope you can help!
Alexandar, have a look at this article:
Hi Anthony,
Thanks! - having read the article I’m still stuck. As I’m pretty new to this it might be to complicated. I guess I understand so far the actions the aggregate does - but where in the code do I tell it where to show the result?
All I want is to sum up a column and show it in a textbox.
Alexandar,
Here is something to get you jump-started. This Sum_amount function is being called in the onReady function of the page, after all of the elements are loaded. The value is placed in an input called “input1” after the successful aggregate query runs. The import line needs to be there in order to use any wixData function. You will need to substitute the real name of the collection for “CollectionName”.
import wixData from ‘wix-data’;
$w.onReady(() => {
Sum_amount();
});
export function Sum_amount(){
wixData.aggregate(“CollectionName”)
.sum(“amount”,“sumamount”)
.run()
.then( (results) => {
$w(‘#input1’).value = results.items[0].sumamount;
} );
}
Anthony, you just saved my day - It works!!
Thank you so much!
I’m using the exact same code and only getting the value for the first row in the column. Two things to note that are probably different, I am filtering that dataset to use on the page and have a table on the page that displays the filtered data. Could this be screwing it up as well? Its also on a dynamic page not a static page. Any help would be greatly appreciated!!
Hi Doug,
That aggregate query is run directly on the collection data as the page loads, so filtering of the dataset would not impact that.
I have not used dynamic pages much, so I’m hesitant to suggest one way or another whether or not that it is the culprit. It would be helpful if you could share your code. Perhaps, the cause of the problem will be obvious to one of us.
Thank you I am a little stumped. Here is my code:
import wixData from ‘wix-data’;
$w.onReady(() => {
Sum_amount();
});
export function Sum_amount(){
wixData.aggregate(‘stallSales’)
.sum(“type1StallsAmount”,“sumamount”)
.run()
.then( (results) => {
$w(‘#input1’).value = results.items[0].sumamount;
} );
}
what is so strange is that it gives me the value of the first cell in the row on the dynamic page. I will say that the routing may be complicated. My page is a dynamic page built from the showInformation dataset. On that page, I have a table on that page that pulls from the stallSales dataset and filters based on the horseshowId matching the ID field from the showInformation page. I hope that makes sense, the idea is that I will next try to integrate stripe into the stallSales portion.
As a test, I’m wondering what the query function returns at that point. Does it similarly return the data from one record only?
$w.onReady(() => {
//Sum_amount();
Query_collection();
});
export function Sum_amount(){
wixData.aggregate(‘stallSales’)
.sum(“type1StallsAmount”,“sumamount”)
.run()
.then( (results) => {
$w(‘#input1’).value = results.items[0].sumamount;
} );
}
export function Query_collection(){
wixData.query(‘stallSales’)
.find()
.then( (results) => {
console.log(results);
} );
}
So this code is working when I do it on that page that is not dynamic:
import wixData from 'wix-data';
$w.onReady(() => {
Sum_amount();
});
export function Sum_amount(){
wixData.aggregate('stallSales')
.sum("type1StallsPurchased","sumamount")
.run()
.then( (results) => {
$w('#input18').value = results.items[0].sumamount;
console.log(results);
} );
}
My issue now I need to put a filter in so that it only adds up some of the rows. In my dataset ‘stallSales’ I have a column for horse show ID. I am on a page that all that data in the table is filtered off of that horse show ID. It is a dynamic page built from the show information dataset. I am just trying to figure out how to use the horse show ID that correlates to the dynamic page I am on to set the filter and I keep coming up blank. Any help would be greatly appreciated.
This has helped immensely ! I’ve spent hours and hours on trying different snippets of code from various posts and this is the most straightforward.
Just one more thing I need help on is how to filter the data collection based on the owner ID of the logged in user. Any ideas?
@barb , is the user ID in a field of the collection that you are wishing to filter?
This will show you how to obtain the ID:
https://www.wix.com/corvid/reference/wix-users.User.html#id
On a dynamic page, you should get the current item form the dynamic dataset (in the onReady event of the dataset, using the getCurrentItem() function). Read from the current item the value for horse show ID. Now, set this value as a filter in the aggregate query above.
UPDATE: I just realized user Id in the private member data is the same as owner ID in my collection.
@tony-brunsman I want to do the same filter as I would on a dataset I’m displaying on the page (where logged in user’s Owner ID (user ID) matches the Owner ID in the data collection). (I tried referencing the filtered data collection in the aggregate function but it wasn’t valid.)
This is my page code (I am a beginning coder, BTW). I’m getting totals for ALL records, not the filtered ones.
UPDATED CODE (8/28):
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; // “user@something.com”
} );
let searchOwner = user.id.value;
let filter = wixData.filter().eq(“_owner”,searchOwner); //I can’t figure out why this isn’t working
$w.onReady(() => {
Sum_amount();
});
export function Sum_amount(){
wixData.aggregate(“MilesHoursLog”)
.filter(filter) //wouldn’t this use the filter I referenced above?
.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
} );
}
Thanks in advance!
@barb This page will also be helpful in showing you how to apply a filtering condition on an aggregate.
@tony-brunsman Thanks for getting me closer (I think) Heath H-M and anthonyb. But now my totals are zero (before they were for the whole data collection. I hope my mistake is little but I can’t figure out what’s wrong (no errors in Preview).
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; // “user@something.com”
} );
const filter = wixData.filter().eq(“owner”,user.id);
$w.onReady(() => {
Sum_amount();
});
export function Sum_amount(){
wixData.aggregate(“MilesHoursLog”)
.filter(filter)
.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 tried a different approach to filtering. Back to total hours for the data collection, not just those for the logged in user. 
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; // “user@something.com”
} );
let searchOwner = user.id.value;
let filter = wixData.filter().eq(“_owner”,searchOwner);
$w.onReady(() => {
Sum_amount();
});
export function Sum_amount(){
wixData.aggregate(“MilesHoursLog”)
.filter(filter)
.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
} );
}
Any ideas on how I can filter the data collection by owner for aggregating hours and miles for the logged in user?
@tony-brunsman (or anyone else who knows how to filter the rows before aggregating the totals.
This is my current 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;
} );
let searchOwner = user.id.value;
let filter = wixData.filter().eq(“_owner”,searchOwner);
$w.onReady(() => {
Sum_amount();
});
export function Sum_amount(){
wixData.aggregate(“MilesHoursLog”)
.filter(filter)
.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
});
}
No errors, but the filter is not working. The aggregated totals are for all records in the collection instead of for those of the logged-in user.
Any help would be greatly appreciated.
Help with getting sum of data base. Step by step. I am trying to create a custom cart page.
