Why is SUM not working on Datamart query
- Former user (Deleted)
- Jan Panoušek
- Jana Volencová (Deactivated)
Question
The SUM function in the Datamart query below is not working as needed.
// Get datamart context
def dmContext = api.getDatamartContext()
// Create datamart query
def dmQueryInvoice = dmContext.newQuery(dmContext.getDatamart("Transactions"), false)
dmQueryInvoice.
select("INVOICE_NUMBER").
select("ACCOUNT_NAME").
select("SUM(FINAL_GIVEN_NET_PRICE)").
where(Filter.equal("INVOICE_DATEYear","2018"),
Filter.equal("BILLING_TYPE","F2"))Answer
You need to add a variable name to the SUM statement to get the sum value from the query result:
select("SUM(FINAL_GIVEN_NET_PRICE)","SUM_FINAL_GIVEN_NET_PRICE")
Also, you should add separate WHERE clauses for each filter.
.where(Filter.equal("INVOICE_DATEYear","2018"))
.where(Filter.equal("BILLING_TYPE","F2"))
Also make sure that all DM fields used in SELECT statements are marked as dimension (in their underlying Data Source).
The second parameter of the method newQuery(Table table, boolean rollup) is important - if its value is FALSE, then there will be no rollup/aggregation happening.
So when you set the rollup param to TRUE, the rollup will happen (i.e., the SUM function will work).
Related content
Found an issue in documentation? Write to us.