Why is SUM not working on Datamart query


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"))


You need to add a variable name to the SUM statement to get the sum value from the query result:


Also, you should add separate WHERE clauses for each filter.



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).

Found an issue in documentation? Write to us.