Use of .aggregate() method together with .sum().

Code

def qapi = api.queryApi()
def exprs = qapi.exprs()
def t1 = qapi.tables().priceGridLineItems(843)

return qapi.source(t1, [
        t1.previousPrice().multiply(t1."Last Year Sales Volume").as("RevenueLastYear"),
        t1.calculatedResultPrice().multiply(t1."Sales Volume Forecast").as("RevenueNextYear"),
        t1."New Margin",])

        .aggregate({ cols ->

                        [exprs.sum(cols.RevenueLastYear).as("RevenueLastYear"),
                         exprs.sum(cols.RevenueNextYear).as("RevenueNextYear"),
                         exprs.sum(cols.RevenueNextYear).minus(exprs.sum(cols.RevenueLastYear)).as("RevenueDiff"),
                         qapi.exprs().min(cols."New Margin").as("Lowest Margin"),
                         qapi.exprs().count().as("TotalItems"),] })

        .stream { it.collect { it } }

Results

RevenueLast	RevenueNextYear	RevenueDiff	LowestMargin	TotalItems

RevenueLast	RevenueNextYear	RevenueDiff	LowestMargin	TotalItems
4790321.1	48875941	440865619.9	0.19	33

Aggregation in Price List and Live Price Grid Headers

Code

Results

See Also