Hibernate count, min, max, sum, avg Functions

Learn to use the Hibernate aggregate functions that can be used in HQL/SQL SELECT statements to query a calculated value for a field among all entities matching the given criteria.

1. Aggregate Functions

Hibernate Query Language (HQL) supports the following aggregate functions in SELECT statements.

Except count(), all other functions except numeric values as arguments. The count() can be used to count any kind of values, including the number of rows in the query result.

• avg(): calculates the average of given numeric arguments.
• count() including count(distinct), count(all), and count(*): count the number of values/rows.
• max(): returns the maximum values among the given numeric arguments.
• min(): returns the minimum values among the given numeric arguments.
• sum(): returns the sum of all the given numeric arguments.

Note that similar to other SQL keywords, these function names are also case-insensitive.

2. Using Aggregate Functions

2.1. Single Aggregate Function

To execute an aggregate function, use it in the HQL query and then use getSingleResult() method that returns the calculated value by the used aggregate function.

Long emailCount = entityManager
    .createQuery("select count(distinct e.email) from Employee e", Long.class)
    .getSingleResult();

2.2. Multiple Aggregate Function

It is also possible to execute more than one aggregate function in a single HQL statement. In this case, the returned value will be an Object[].

Object[] minAndMaxSalaries = entityManager.createQuery(
      "select " +
      " min(e.salary), " +
      " max(e.salary) " +
      "from Employee e ",
      Object[].class)
  .getSingleResult();

2.3. Combine with Other Functions

We may use arithmetic operators, concatenation, and other recognized SQL functions in the SELECT clause, along with the listed aggregate functions.

Long maxSalaryWithBonus = entityManager
  .createQuery("SELECT MAX(e.salary + e.bonus) FROM Employee e WHERE e.bonus > 100000", Long.class)
  .getSingleResult();

3. Conclusion

In this tutorial, we learned to use the hibernate aggregate functions in different ways. These functions help in consolidating the returned rows in a single value result.

Happy Learning !!

Sourcecode on Github