GROUP BY and HAVING clauses

The GROUP BY clause enables grouping of query results. A JPQL query with a GROUP BY clause returns properties of generated groups instead of individual objects and fields.

The position of a GROUP BY clause in the query execution order is after the FROM and WHERE clauses, but before the SELECT clause. When a GROUP BY clause exists in a JPQL query, database objects (or tuples of database objects) that are generated by the FROM clause iteration and pass the WHERE clause filtering (if any) are sent to grouping by the GROUP BY clauses before arriving at the SELECT clause.

GROUP BY as DISTINCT (no Aggregates)

The following query groups all the countries by their first letter:

SELECT SUBSTRING(c.name, 1, 1)
FROM Country c
GROUP BY SUBSTRING(c.name, 1, 1);

The FROM clause defines iteration over all the Country objects in the database. The GROUP BY clause groups these Country objects by the first letter of the country name. The next step is to pass the groups to the SELECT clause which returns the first letters as result.

ObjectDB is very flexible in allowing JPQL expressions anywhere in the query. Note that the query above might not be supported by some JPA implementations. Only identification variables and path expressions are currently supported in the GROUP BY clause by all the JPA implementations.

Grouping the Country objects makes them inaccessible to the SELECT clause as individuals. Therefore, the SELECT clause can only use properties of the groups, which include:

• The properties that are used for grouping (each group has a unique value combination).
• Aggregate calculations (count, sum, avg, max, min) that are carried out on all the objects (or the object tuples) in the group.

The aggregate calculation gives the GROUP BY clause its power. Actually, without aggregate calculations - the GROUP BY functions merely as a DISTINCT operator. For example, the above query (which does not use aggregates) is equivalent to the following query:

SELECT DISTINCT SUBSTRING(c.name, 1, 1) FROM Country c

GROUP BY with Aggregate Functions

JPQL supports the five aggregate functions of SQL:

• COUNT - returns a long value representing the number of elements.
• SUM - returns the sum of numeric values.
• AVG - returns the average of numeric values as a double value.
• MIN - returns the minimum of comparable values (numeric, strings, dates).
• MAX - returns the maximum of comparable values (numeric, strings, dates).

The following query counts for every letter the number of countries with names that start with that letter and the number of different currencies that are used by these countries:

SELECT SUBSTRING(c.name, 1, 1), COUNT(c), COUNT(DISTINCT c.currency)
FROM Country c
GROUP BY SUBSTRING(c.name, 1, 1);

The query returns Object[] arrays of length 3, in which the first cell contains the initial letter as a String object, the second cell contains the number of countries in that letter's group as a Long object and the third cell contains the distinct number of currencies that are in use by countries in that group. The DISTINCT keyword in a COUNT aggregate expression, as demonstrated above), eliminates duplicate values when counting.

Only the COUNT aggregate function can be applied to entity objects directly. Other aggregate functions are applied to fields of objects in the group by using path expressions.

The following query groups countries in Europe by their currency, and for each group returns the currency and the cumulative population size in countries that use that currency:

SELECT c.currency, SUM(c.population)
FROM Country c
WHERE 'Europe' MEMBER OF c.continents
GROUP BY c.currency

Because grouping is performed in this query on a path expression, this query is standard and it is expected to be supported by all JPA implementations.

GROUP BY with HAVING

Groups in JPQL grouping queries can be filtered using the HAVING clause. The HAVING clause for the GROUP BY clause is like the WHERE clause for the FROM clause. ObjectDB supports the HAVING clause only when a GROUP BY clause exists.

The following query uses HAVING to change the previous query in a way that single country groups are ignored:

SELECT c.currency, SUM(c.population)
FROM Country c
WHERE 'Europe' MEMBER OF c.continents
GROUP BY c.currency
HAVING COUNT(c) > 1

The HAVING clause stands as a filter between the GROUP BY clause and the SELECT clause in such a way that only groups that are accepted by the HAVING filter are passed to the SELECT clause. The same restrictions on SELECT clause in grouping queries also apply to the HAVING clause, which means that individual object fields are inaccessible. Only group properties which are the expressions that are used for grouping (e.g. c.currency) and aggregate expressions are allowed in the HAVING clause.

Global Aggregates (no GROUP BY)

JPQL supports a special form of aggregate queries that do not have a GROUP BY clause in which all the FROM/WHERE objects (or object tuples) are considered as one group.

For example, the following query returns the sum and average population in countries that use the English language:

SELECT SUM(c.population), AVG(c.population)
FROM Country c
WHERE 'English' MEMBER OF c.languages

All the Country objects that pass the FROM/WHERE phase are considered as one group, for which the cumulative population size and the average population size is then calculated. Any JPQL query that contains an aggregate expression in the SELECT clause is considered a grouping query with all the attached restrictions, even when a GROUP BY clause is not specified. Therefore, in this case, only aggregate functions can be specified in the SELECT clause and individual objects and their fields become inaccessible.

GROUP BY and HAVING in Criteria Queries

The CriteriaQueryjavax.persistence.criteria.CriteriaQueryJPA interfaceThe CriteriaQuery interface defines functionality that is specific to top-level queries.See JavaDoc Reference Page... interface provides methods for setting the GROUP BY and HAVING clauses.

For example, the following JPQL query:

SELECT c.currency, SUM(c.population)
FROM Country c
WHERE 'Europe' MEMBER OF c.continents
GROUP BY c.currency
HAVING COUNT(c) > 1

can be built using the criteria query API as follows:

  CriteriaQueryjavax.persistence.criteria.CriteriaQueryJPA interfaceThe CriteriaQuery interface defines functionality that is specific 
 to top-level queries.See JavaDoc Reference Page...<Country> q = cb.createQuerycreateQuery(resultClass)CriteriaBuilder's methodCreate a CriteriaQuery object with the specified result 
  type.See JavaDoc Reference Page...(Country.class);
  Rootjavax.persistence.criteria.RootJPA interfaceA root type in the from clause.See JavaDoc Reference Page...<Country> c = q.fromfrom(entityClass)AbstractQuery's methodCreate and add a query root corresponding to the given entity,
 forming a cartesian product with any existing roots.See JavaDoc Reference Page...(Country.class);
  q.multiselectmultiselect(selections)CriteriaQuery's methodSpecify the selection items that are to be returned in the 
 query result.See JavaDoc Reference Page...(c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("currency"), cb.sumsum(x)CriteriaBuilder's methodCreate an aggregate expression applying the sum operation.See JavaDoc Reference Page...(c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("population")));
  q.wherewhere(restriction)CriteriaQuery's methodModify the query to restrict the query result according
 to the specified boolean expression.See JavaDoc Reference Page...(cb.isMemberisMember(elem, collection)CriteriaBuilder's methodCreate a predicate that tests whether an element is
  a member of a collection.See JavaDoc Reference Page...("Europe", c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("continents")));
  q.groupBygroupBy(grouping)CriteriaQuery's methodSpecify the expressions that are used to form groups over
 the query results.See JavaDoc Reference Page...(c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("currency"));
  g.havinghaving(restriction)CriteriaQuery's methodSpecify a restriction over the groups of the query.See JavaDoc Reference Page...(cb.gtgt(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 greater than the second.See JavaDoc Reference Page...(cb.countcount(x)CriteriaBuilder's methodCreate an aggregate expression applying the count operation.See JavaDoc Reference Page...(c), 1));

The CriteriaBuilderjavax.persistence.criteria.CriteriaBuilderJPA interfaceUsed to construct criteria queries, compound selections, expressions, predicates, orderings.See JavaDoc Reference Page... interface provides methods for building aggregate expressions:

• countcount(x)CriteriaBuilder's methodCreate an aggregate expression applying the count operation.See JavaDoc Reference Page..., countDistinctcountDistinct(x)CriteriaBuilder's methodCreate an aggregate expression applying the count distinct operation.See JavaDoc Reference Page... - return a long type expression representing the number of elements.
• sumsum(x)CriteriaBuilder's methodCreate an aggregate expression applying the sum operation.See JavaDoc Reference Page... , sumAsLongsumAsLong(x)CriteriaBuilder's methodCreate an aggregate expression applying the sum operation to an Integer-valued expression, returning a Long result.See JavaDoc Reference Page..., sumAsDoublesumAsDouble(x)CriteriaBuilder's methodCreate an aggregate expression applying the sum operation to a Float-valued expression, returning a Double result.See JavaDoc Reference Page... - return an expression representing the sum of values.
• avgavg(x)CriteriaBuilder's methodCreate an aggregate expression applying the avg operation.See JavaDoc Reference Page... - returns a double type expression representing the average of numeric values.
• minmin(x)CriteriaBuilder's methodCreate an aggregate expression applying the numerical min operation.See JavaDoc Reference Page..., leastleast(x)CriteriaBuilder's methodCreate an aggregate expression for finding the least of the values (strings, dates, etc).See JavaDoc Reference Page... - return an expression representing the minimum of comparable values.
• maxmax(x)CriteriaBuilder's methodCreate an aggregate expression applying the numerical max operation.See JavaDoc Reference Page..., greatestgreatest(x)CriteriaBuilder's methodCreate an aggregate expression for finding the greatest of the values (strings, dates, etc).See JavaDoc Reference Page... - return an expression representing the maximum of comparable values.

The groupBygroupBy(grouping)CriteriaQuery's methodSpecify the expressions that are used to form groups over the query results.See JavaDoc Reference Page... method takes a variable number of arguments specifying one or more grouping expressions (or a list of expressions in another form of groupBygroupBy(grouping)CriteriaQuery's methodSpecify the expressions that are used to form groups over the query results.See JavaDoc Reference Page...).

Setting a HAVING clause is very similar to setting a WHERE clause. As with the WHERE clause - two forms of the having method are provided. One havinghaving(restriction)CriteriaQuery's methodSpecify a restriction over the groups of the query.See JavaDoc Reference Page... form takes an Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<Boolean> argument and the other havinghaving(restrictions)CriteriaQuery's methodSpecify restrictions over the groups of the query according the conjunction of the specified restriction predicates.See JavaDoc Reference Page... form takes a variable number of Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or disjunction of restrictions.See JavaDoc Reference Page... arguments (and uses an AND conjunction).

When a groupBy or a having method is invoked, previously set values (if any) are discarded.