Linked Questions

Popular Questions

Spring Data JPA aggregate functions on an empty resultset

Asked by At

I am working on a project involving Spring and JPA/Hibernate. The database driver used in my development environment is H2. My application has a page that displays statistics, one example of such a statistic is the average age of my users. However, when I try to fetch the average age using JPQL, I receive an exception

Result must not be null!

Assume for simplicity reasons that I store age as an integer on every User object (in my application this is of course not the case, but that's not important for my problem).

User model

public class User implements Identifiable<Long> {
    private int age;
    // more fields and methods, irrelevant

User repository

public interface UserRepository extends CrudRepository<User, Long> {
    @Query("SELECT AVG(u.age) FROM #{#entityName} u")
    long averageAge();

I cannot seem to figure out why calling UserRepository#averageAge(); is throwing the exception. I have tried replacing the function AVG in the query by COUNT and this behaves as expected. I have also tried to use an SQL query and setting nativeQuery = true in the annotation, yet to no avail. I can ofcourse solve it by fetching all the users and calculate the average age in plain Java, but this wouldn't be very efficient.


Caused by: org.springframework.dao.EmptyResultDataAccessException: Result must not be null!
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(
    at com.sun.proxy.$Proxy150.averageAge(Unknown Source)
at org.springframework.boot.SpringApplication.callRunner(
    ... 30 more


The exception was caused by the fact that AVG() returns null when performed on an empty table. I fixed it by modifying the query (inspired by the answer to this question) as follows:

@Query("SELECT coalesce(AVG(u.age), 0) FROM #{#entityName} u")
long averageAge();

Related Questions