11.7. Aggregates

Aggregates are just what they sound like: aggregations of data from multiple instances. Combined with object filtering and grouping, aggregates are powerful tools for summarizing your persistent data.

JDOQL includes the following aggregate functions:


Kodo allows you to define your own aggregate functions. See Section 10.7, “Query Extensions” in the Reference Guide for details.

The following example counts the number of magazines that cost under 5 dollars:

Example 11.17. Count

Query query = pm.newQuery (Magazine.class, "price < 5");
query.setResult ("count(this)");
Long count = (Long) query.execute ();

You may be thinking that we could have gotten the count just as easily by executing a standard query and calling Collection.size () on the result, and you'd be right. But not all aggregates are so easy to replace. Our next example retrieves the minimum, maximum, and average magazine prices in the database. These values would be a little more difficult to calculate manually. More importantly, iterating over every single persistent magazine in order to factor its price into our calculations would be woefully inefficient.

Example 11.18. Min, Max, Avg

Query query = pm.newQuery (Magazine.class);
query.setResult ("min(price), max(price), avg(price)");
Object[] prices = (Object[]) query.execute ();
Double min = (Double) prices[0];
Double max = (Double) prices[1];
Double avg = (Double) prices[2];

The functionality described above is useful, but aggregates only really shine when you combine them with object grouping.

public void setGrouping (String grouping);

The Query interface's setGrouping method allows you to group query results on field values. The grouping string consists of one or more comma-separated clauses to group on, optionally followed by the having keyword and a boolean expression. The having expression pares down the candidate groups just as the query's filter pares down the candidate objects.

Now your aggregates apply to each group, rather than to all matching objects. Let's see this in action:

Example 11.19. Grouping

The following query returns each publisher and the average price of its magazines, for all publishers that publish no more than 10 magazines.

Query query = pm.newQuery (Magazine.class);
query.setResult ("publisher, avg(price)");
query.setGrouping ("publisher having count(this) <= 10");
Collection results = (Collection) query.execute ();
for (Iterator itr = results.iterator (); itr.hasNext ();)
    Object[] data = (Object[]) itr.next ();
    processData ((Company) data[0], (Double) data[1]);
query.close (results);

You probably noticed that in our initial aggregate examples, the queries all returned a single result object, while the query above returned a Collection. Before you get too confused, let's take a brief detour to examine query return types.

In addition to how many results are returned, query configuration can affect the type of each result:

Don't worry about trying to memorize all of these rules. In practice, they amount to a much simpler rule: queries return what you expect them to. A query for all the magazines that match a filter returns a collection of Magazines. But an aggregate query for the count of all magazines that match a filter just returns a Long. A projection query for the title of all magazines returns a collection of Strings. But a projection for both the title and price of each magazine returns a collection of Object[]s, each consisting of a String and a Double. So although you can always explicitly set the unique flag and result class to obtain a specific result shape, the defaults are usually exactly what you want.