How to calculate average on a column with JPA?

Question

I would like to calculate average on a column. I tried the following:

@Query("SELECT AVG(e.rating) FROM user_rating e WHERE e.route_uid = ?1")
fun averageOfRateings(routeId: UUID): Long

The query works in Sql, however I get the following error when I run the code in Spring Boot.

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException:
user_rating is not mapped [SELECT AVG(e.rating) FROM user_rating e WHERE e.route_uid = ?1] 

What would be the right syntax? What is the problem with the mapping of my table?

Answer 1

you must use the entity name, not the table.The same applies for the column name, you must use the field name instead.

I assume your entity is UserRating, so the correct query will be:

@Query("SELECT AVG(e.rating) FROM UserRating e WHERE e.routeUid = ?1") 

Or second option specify that is native query:

@Query(value = "SELECT AVG(e.rating) FROM user_rating e WHERE e.route_uid = ?1" , nativeQuery = true)