I want to calculate the sum of all orders for each customer in a specified time period.
Right know I’m doing it whit databaseManager.getDataSetByQuery and the following SQL query.
SELECT sum(value) as sumValue, customerID
FROM orders
WHERE orders.date >= ? and orders.date <= ?
group by customerID
The result of this query is, for each customerID I get a sum of all his orders.
Now I want to convert this to use FoundSetFilterParam, because of compatibly issues (worrying that sum or other SQL functions might not work on other databases)
var _myFs = databaseManager.getFoundSet('myDB','orders')
_myFs.addFoundSetFilterParam('date','>=',_firstLastDateArray[0])
_myFs.addFoundSetFilterParam('date','<=',_firstLastDateArray[1])
_myFs.loadAllRecords()
Also I created an Aggregation sumValue on the orders table.
Whit _myFs.getRecord(1).sumValue I getting the sum of all the orders from all the customers, but I want to have a list from each customer whit his sumValue as I get it whit the SQL script.
Any ideas?
ttmgybta