JPQL @Query causing "unexpected token: ("

Question

I have following method defined:

@Query("SELECT AVG(total) FROM (SELECT COUNT(t.name) total FROM DataTable t GROUP BY DATE(actiontime)) result")
Long countAvg();

However it causes this error:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected     token: ( near line 1, column 24 [SELECT AVG(total) FROM (SELECT     COUNT(t.name) total FROM backend.DataTable t GROUP BY     DATE(actiontime)) result]

But following SQL works fine:

SELECT AVG(total) FROM (SELECT COUNT(NAME) total FROM DATA_TABLE GROUP BY DATE(actiontime)) result 

If i understand correctly, JPQL has problems with subquery. How should i create this kind of query then?

Answer 1

I don't think jpql supports subselect with from clause. As per jpa docs

Subqueries are restricted to the WHERE and HAVING clauses in this release. Support for subqueries in the FROM clause will be considered in a later release of the specification.

You can use nativeQuery = true in the @Query annotation and run it as native query instead or rewrite the query if possible.