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