Why is SUM not working on Datamart query

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

Found an issue in documentation? Write to us.