I want to get the number of entries where the score and date columns are in a given range, grouped by score intervals. Score intervals are determined by the given granularity. So the question I want to answer is: how many results are there, for the given time period, with scores between 0-25, 26-50, 56-75, etc... where in this case, the granularity would be 25.
The following native SQL query yields the desired results:
SELECT result.score, COUNT(*), ((score-1) DIV 25) AS eqclass FROM v_result AS result GROUP BY (eqclass);
I'd like to define a query method with my own query, using @Query annotation.
@Query("SELECT new com.example.ScoreDistribution(COUNT(scoreStatistics.score), (scoreStatistics.score - 1) / :granularity) FROM ScoreStatisticsEntity scoreStatistics WHERE start_date BETWEEN :startDate AND :endDate GROUP BY ((scoreStatistics.score - 1) / :granularity)")
List<ScoreDistribution> calculateDistributionByGranularity(@Param("startDate") DateTime startDate, @Param("endDate") DateTime endDate, @Param("granularity") int granularity);
ScoreDistribution is a simple bean, it is not an entity. ScoreStatisticsEntity however is an entity, and it is backed by a view table.
I get the following exception:
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Hibernate API doc states:
public class SQLGrammarException extends JDBCException
Implementation of JDBCException indicating that the SQL sent to the database server was invalid (syntax error, invalid object references, etc).
So, the question is: what is wrong with my query?
WHERE start_date should be something like WHERE scoreStatistics.startDate