Skip to main content

Aggregation

Apply provided functions to values to calculate group values in the grid.

Backend source code of example grids available here

Enabling Aggregation​

To make column available for aggregation, set the enableValue parameter to true on ColDef, otherwise aggregation attempt on this column will result to runtime exception.

To limit which aggregation functions (AggregationFunction) can be used on the column, provide array of allowed functions (otherwise all the aggregation functions are allowed.)

ColDef priceColumn = ColDef.builder()
    .field("price")
    .enableValue(true)
    .allowedAggFuncs(AggregationFunction.avg, AggregationFunction.count)
    // or just use string names
    // .allowedAggFuncs("avg", "count")
    .build();
  • Source code for this grid available here
Loading grid...

⚠️ Disclaimer Currently aggregation functions first and last are not supported in JPA. Using these functions will result in runtime exception. Supported functions are: avg, sum, min, max, count.

Custom Aggregation Functions​

In addition to the built-in functions, the adapter allows you to register custom aggregation functions or overwrite existing ones. This is particularly useful for leveraging database-specific functions (e.g., statistical functions) that are not part of the standard JPA set.

Custom functions are registered within the QueryBuilder using the .registerCustomAggFunction() method. The following example demonstrates registering functions specifically for the H2 database.

QueryBuilder<Entity> queryBuilder = QueryBuilder.builder(Entity.class, entityManager)
    .colDefs(colDefs)
    // Registering custom aggregation functions
    .registerCustomAggFunction("bool_and", (cb, expr) -> cb.function("BOOL_AND", Boolean.class, expr))
    .registerCustomAggFunction("stddev_pop", (cb, expr) -> cb.function("STDDEV_POP", BigDecimal.class, expr))
    .registerCustomAggFunction("stddev_samp", (cb, expr) -> cb.function("STDDEV_SAMP", BigDecimal.class, expr))
    .build();
  • Source code for this grid available here
Loading grid...

Aggregation - Filtering​

Filtering can be configured to impact aggregate values in the grid.

Ignore Filters when Aggregating​

When using Filters and Aggregations together, the aggregated values reflect only the rows which have passed the filter. This can be changed to instead ignore applied filters by using the suppressAggFilteredOnly grid option.

QueryBuilder<Entity> queryBuilder = QueryBuilder.builder(Entity.class, entityManager)
    .colDefs(
        // ...col defs
    )
    .suppressAggFilteredOnly(true)
    .build();
  • Source code for this grid available here
Loading grid...

Filtering for Aggregated Values​

The grid only applies filters to leaf level rows, this can be toggled to instead also apply filtering to group rows by enabling the groupAggFiltering grid option, allowing filters to also apply against the aggregated values.

QueryBuilder<Entity> queryBuilder = QueryBuilder.builder(Entity.class, entityManager)
    .colDefs(
        // ...col defs
    )
    .groupAggFiltering(true)
    .build();
  • When a group row passes a filter, it also includes all of its descendent rows in the filtered results.
  • The suppressAggFilteredOnly grid option will be implicitly enabled.
  • Source code for this grid available here
Loading grid...