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.CriteriaQuery - JPA InterfaceThe CriteriaQuery interface defines functionality that is specific to top-level queries. 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.CriteriaQuery - JPA InterfaceThe CriteriaQuery interface defines functionality that is specific
 to top-level queries.<Country> q = cb.createQueryCriteriaBuilder.createQuery(resultClass) - JPA MethodCreate a CriteriaQuery object with the specified result
  type.(Country.class);
  Rootjavax.persistence.criteria.Root - JPA InterfaceA root type in the from clause.<Country> c = q.fromAbstractQuery.from(entityClass) - JPA MethodCreate and add a query root corresponding to the given entity,
 forming a cartesian product with any existing roots.(Country.class);
  q.multiselectCriteriaQuery.multiselect(selections) - JPA MethodSpecify the selection items that are to be returned in the
 query result.(c.getPath.get(attributeName) - JPA MethodCreate a path corresponding to the referenced attribute.("currency"), cb.sumCriteriaBuilder.sum(x) - JPA MethodCreate an aggregate expression applying the sum operation.(c.getPath.get(attributeName) - JPA MethodCreate a path corresponding to the referenced attribute.("population")));
  q.whereCriteriaQuery.where(restriction) - JPA MethodModify the query to restrict the query result according
 to the specified boolean expression.(cb.isMemberCriteriaBuilder.isMember(elem,collection) - JPA MethodCreate a predicate that tests whether an element is
  a member of a collection.("Europe", c.getPath.get(attributeName) - JPA MethodCreate a path corresponding to the referenced attribute.("continents")));
  q.groupByCriteriaQuery.groupBy(grouping) - JPA MethodSpecify the expressions that are used to form groups over
 the query results.(c.getPath.get(attributeName) - JPA MethodCreate a path corresponding to the referenced attribute.("currency"));
  g.havingCriteriaQuery.having(restriction) - JPA MethodSpecify a restriction over the groups of the query.(cb.gtCriteriaBuilder.gt(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 greater than the second.(cb.countCriteriaBuilder.count(x) - JPA MethodCreate an aggregate expression applying the count operation.(c), 1));

The CriteriaBuilderjavax.persistence.criteria.CriteriaBuilder - JPA InterfaceUsed to construct criteria queries, compound selections, expressions, predicates, orderings. interface provides methods for building aggregate expressions:

The groupByCriteriaQuery.groupBy(grouping) - JPA MethodSpecify the expressions that are used to form groups over the query results. method takes a variable number of arguments specifying one or more grouping expressions (or a list of expressions in another form of groupByCriteriaQuery.groupBy(grouping) - JPA MethodSpecify the expressions that are used to form groups over the query results.).

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 havingCriteriaQuery.having(restriction) - JPA MethodSpecify a restriction over the groups of the query. form takes an Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Boolean> argument and the other havingCriteriaQuery.having(restrictions) - JPA MethodSpecify restrictions over the groups of the query according the conjunction of the specified restriction predicates. form takes a variable number of Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. arguments (and uses an AND conjunction).

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