Group by and Query Functions

You can download the code for this example here:

OnyxDevTools/onyx-database-examples/querying/GroupByExample.

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

OnyxDevTools/onyx-database-examples/querying/KotlinQueryBuilder.kt.

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)

   Notes:

   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