Group by and Query Functions

You can download the code for this example here:


The Group By statement is often used with aggregate functions (count, min, max, sum, avg) to group the result-set by one or more columns. Onyx Database 2.1.1 supports grouping by one or more attributes or derived attributes. Both Kotlin and Java support this new feature. A Kotlin example can be found


  1. Define a Query with criteria
  2. Add a Group By attribute

    The group by will allow one or more fields. The fields can also be derived. For instance, you can group by replace("myAttribute", "\\s+", "").

    In this example we have grouped by rushing yards to see how many players have specific yards. To do so, you must select rushingYards and count(player.playerId)

    The groupBy in this case has taken a string value representing the attribute name.
    The count method consist of a string value "count(playerId)". With Java functions are included by using their string value. The supported functions are.
    count Get the number of values matching criteria.
    min Get the minimum value of all the matching values
    max Get the maximum value of all the values
    avg Get the average of all the values matching criteria
    sum Get the sum of all of the values matching criteria
    replace(param1, param2) Perform a string replace
    substring(start, length) Get the substring of a field value
    The following apply to the groupBy modifier.
    count, min, max, avg, and sum
    The distinct() modifier only applies to
    count, replace, substring
  3. Invoke the PersistenceManager#executeQuery method

    This will retrieve a List<Map<String, Object>>.

    The data will be formatted as with the keys being the selections.

    Aliases are not supported yet.
  4. The Kotlin DSL Query equivalent would be
    The count() function is a method invocation rather than a string value.
  5. Query functions without Group By

    Another example of using Query functions without groupBy is to use the max() method to get the leading rusher.

  6. Kotlin Query Functions

    The example above can also be written in Kotlin using the following:

  7. Partitioning