title: Spring Data JPA aggregate functions on an empty resultset
body: <p>I am working on a project involving <code>Spring</code> and <code>JPA/Hibernate</code>. The database driver used in my development environment is <code>H2</code>. 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 <code>JPQL</code>, I receive an exception </p>
  
  <pre><code>Result must not be null!
  </code></pre>
  
  <p>Assume for simplicity reasons that I store age as an <code>integer</code> on every <code>User</code> object (in my application this is of course not the case, but that's not important for my problem).</p>
  
  <p><strong>User model</strong></p>
  
  <pre><code>@Entity
  public class User implements Identifiable&lt;Long&gt; {
      private int age;
      // more fields and methods, irrelevant
  }
  </code></pre>
  
  <p><strong>User repository</strong></p>
  
  <pre><code>@Repository
  public interface UserRepository extends CrudRepository&lt;User, Long&gt; {
      @Query("SELECT AVG(u.age) FROM #{#entityName} u")
      long averageAge();
  }
  </code></pre>
  
  <p>I cannot seem to figure out why calling <code>UserRepository#averageAge();</code> is throwing the exception. I have tried replacing the function <code>AVG</code> in the query by <code>COUNT</code> and this behaves as expected. I have also tried to use an SQL query and setting <code>nativeQuery = true</code> 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.</p>
  
  <p><strong>Stacktrace:</strong></p>
  
  <pre><code>Caused by: org.springframework.dao.EmptyResultDataAccessException: Result must not be null!
      at org.springframework.data.repository.core.support.MethodInvocationValidator.invoke(MethodInvocationValidator.java:102)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
      at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
      at com.sun.proxy.$Proxy150.averageAge(Unknown Source)
      at my.test.application.StatisticsRunner.run(StatisticsRunner.java:72)
  at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:809)
      ... 30 more
  </code></pre>
  
  <p><strong>Solved</strong></p>
  
  <p>The exception was caused by the fact that <code>AVG()</code> returns <code>null</code> when performed on an empty table. I fixed it by modifying the query (inspired by <a href="https://stackoverflow.com/questions/27329843/how-to-use-isnull-in-jpa">the answer to this question</a>) as follows:</p>
  
  <pre><code>@Query("SELECT coalesce(AVG(u.age), 0) FROM #{#entityName} u")
  long averageAge();
  </code></pre>