GROUP BY and HAVING clauses

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

In the query execution order, the GROUP BY clause is processed after the FROM and WHERE clauses but before the SELECT clause. When a query includes a GROUP BY clause, the database objects (or tuples) that result from the FROM clause and pass the WHERE clause filter are grouped before being passed to the SELECT clause.

This page covers the following topics:

GROUP BY as DISTINCT (no aggregates)GROUP BY with aggregate functionsGROUP BY with HAVINGGlobal aggregates (no GROUP BY)GROUP BY and HAVING in criteria queries

GROUP BY as DISTINCT (no aggregates)

The following query groups all countries by their first letter:

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

The FROM clause iterates over all Country objects in the database. The GROUP BY clause groups these Country objects by the first letter of their names. The SELECT clause then receives these groups and returns the first letters as the result.

Although ObjectDB allows JPQL expressions almost anywhere in a query, the preceding query might not be supported by other JPA implementations. Currently, only identification variables and path expressions are guaranteed to be supported in the GROUP BY clause across all JPA implementations.

Grouping the Country objects makes individual objects inaccessible to the SELECT clause. Therefore, the SELECT clause can only use group properties, such as:

  • The properties used for grouping, where each group has a unique combination of values.
  • Aggregate calculations (COUNT, SUM, AVG, MAX, MIN) that are performed on all the objects or object tuples in the group.

Aggregate calculations are what make the GROUP BY clause powerful. Without them, GROUP BY functions as a DISTINCT operator. For example, the preceding 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 that represents the number of elements.
  • SUM: Returns the sum of numeric values.
  • AVG: Returns the average of numeric values as a double.
  • MIN: Returns the minimum of comparable values, such as numbers, strings, and dates.
  • MAX: Returns the maximum of comparable values, such as numbers, strings, and dates.

For each starting letter, the following query counts the number of countries whose names start with that letter and the number of distinct currencies that those countries use:

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 three. The first element in each array is a String object for the initial letter, the second is a Long object for the number of countries in that group, and the third is the number of distinct currencies that the countries in that group use. As shown in the query, using the DISTINCT keyword in a COUNT aggregate expression eliminates duplicate values from the count.

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

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

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

Because this query performs grouping on a path expression, it is a standard query that should be supported by all JPA implementations.

GROUP BY with HAVING

The HAVING clause filters the groups that result from a GROUP BY clause. The HAVING clause is to GROUP BY what the WHERE clause is to FROM. In ObjectDB, the HAVING clause is only supported when a GROUP BY clause is also present.

The following query modifies the previous query by using HAVING to ignore groups that contain only a single country:

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 acts as a filter between the GROUP BY and SELECT clauses. Only groups that satisfy the HAVING condition are passed to the SELECT clause. The HAVING clause has the same restrictions as the SELECT clause in a grouping query, meaning individual object fields are inaccessible. Only group properties, such as the expressions used for grouping (for example, c.currency), and aggregate expressions are allowed in the HAVING clause.

Global aggregates (no GROUP BY)

JPQL supports a special form of aggregate query that does not have a GROUP BY clause. In these queries, all objects or tuples that result from the FROM and WHERE clauses are treated as a single group.

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

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

All Country objects that pass the FROM/WHERE phase are treated as a single group, for which the total and average population are calculated. Any JPQL query with an aggregate expression in its SELECT clause is considered a grouping query, even without a GROUP BY clause, and is subject to the same restrictions. Therefore, the SELECT clause can only contain aggregate functions, and individual objects and their fields are inaccessible.

GROUP BY and HAVING in criteria queries

The CriteriaQueryjakarta.persistence.criteria.CriteriaQueryThe 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:

  CriteriaQueryjakarta.persistence.criteria.CriteriaQueryThe  CriteriaQuery  interface defines functionality that is specific to top-level queries.<Country> q = cb.createQueryjakarta.persistence.criteria.CriteriaBuilder.createQuery(Class)Create a   CriteriaQuery<T>   object with the given result type.(Country.class);
  Rootjakarta.persistence.criteria.RootA root type in the from clause.<Country> c = q.fromjakarta.persistence.criteria.AbstractQuery.from(Class)Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.(Country.class);
  q.multiselectjakarta.persistence.criteria.CriteriaQuery.multiselect(Selection...)Specify the selection items that are to be returned in the query result.(c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("currency"), cb.sumjakarta.persistence.criteria.CriteriaBuilder.sum(Expression)Create an aggregate expression applying the sum operation.(c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("population")));
  q.wherejakarta.persistence.criteria.CriteriaQuery.where(Expression)Modify the query to restrict the query result according to the specified boolean expression.(cb.isMemberjakarta.persistence.criteria.CriteriaBuilder.isMember(E,Expression)Create a predicate that tests whether an element is a member of a collection.("Europe", c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("continents")));
  q.groupByjakarta.persistence.criteria.CriteriaQuery.groupBy(Expression...)Specify the expressions that are used to form groups over the query results.(c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("currency"));
  g.havingjakarta.persistence.criteria.CriteriaQuery.having(Expression)Specify a restriction over the groups of the query.(cb.gtjakarta.persistence.criteria.CriteriaBuilder.gt(Expression,Number)Create a predicate for testing whether the first argument is greater than the second.(cb.countjakarta.persistence.criteria.CriteriaBuilder.count(Expression)Create an aggregate expression applying the count operation.(c), 1));

The CriteriaBuilderjakarta.persistence.criteria.CriteriaBuilderUsed to construct criteria queries, compound selections, expressions, predicates, orderings. interface provides methods for building aggregate expressions:

The groupByjakarta.persistence.criteria.CriteriaQuery.groupBy(Expression...)Specify the expressions that are used to form groups over the query results. method takes a variable number of arguments that specify one or more grouping expressions. An overloaded version of groupByjakarta.persistence.criteria.CriteriaQuery.groupBy(List)Specify the expressions that are used to form groups over the query results. accepts a list of expressions.

Setting a HAVING clause is similar to setting a `WHERE` clause. As with the where method, two forms of the having method are provided. One form takes an Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Boolean> argument, and the other form takes a variable number of Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. arguments, which are combined using an AND conjunction.

When the groupBy or having method is called, any previously set values are discarded.

< JPQL WHERE^ Query StructureJPQL ORDER BY >