Using parameters in JPQL query with aggregate functions-CodePudding

I use JPQL queries in my SpringBoot project. I have a query with COUNT function. The query is:

@Query(value =
    "SELECT new ua.edu.chdtu.deanoffice.service.course.selective.statistics.StudentsRegistrationOnCoursesPercent("  
            "2021 - scsd.studentDegree.studentGroup.creationYear   scsd.studentDegree.studentGroup.realBeginYear, "  
            "COUNT(DISTINCT scsd.studentDegree.id)) "  
            "FROM SelectiveCoursesStudentDegrees AS scsd "  
            "GROUP BY scsd.selectiveCourse.studyYear, scsd.studentDegree.specialization.degree.id, "  
            "scsd.studentDegree.active, "  
            "2021 - scsd.studentDegree.studentGroup.creationYear   scsd.studentDegree.studentGroup.realBeginYear "  
            "having scsd.selectiveCourse.studyYear = :studyYear AND "  
            "scsd.studentDegree.specialization.degree.id = :degreeId AND "  
            "scsd.studentDegree.active = true")
List<StudentsRegistrationOnCoursesPercent> findPercentStudentsWhoChosenSelectiveCourse(@Param("studyYear") int studyYear,
                                                                                           @Param("degreeId") int degreeId);

And it works fine. But when I change a literal 2021 to a JPQL query parameter :currYear (only substitute literal with parameter, nothing else), I get an error:

2022-04-13 15:10:17 [XNIO-2 task-1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ERROR: column "studentgro2_.creation_year" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 11
2022-04-13 15:10:17 [XNIO-2 task-1] ERROR u.e.c.d.a.g.ExceptionHandlerAdvice - ERROR
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:261)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:503)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:209)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)

How can I fix it? The changed query is the following:

@Query(value =
    "SELECT new ua.edu.chdtu.deanoffice.service.course.selective.statistics.StudentsRegistrationOnCoursesPercent("  
            ":currYear - scsd.studentDegree.studentGroup.creationYear   scsd.studentDegree.studentGroup.realBeginYear, "  
            "COUNT(DISTINCT scsd.studentDegree.id)) "  
            "FROM SelectiveCoursesStudentDegrees AS scsd "  
            "GROUP BY scsd.selectiveCourse.studyYear, scsd.studentDegree.specialization.degree.id, "  
            "scsd.studentDegree.active, "  
            ":currYear - scsd.studentDegree.studentGroup.creationYear   scsd.studentDegree.studentGroup.realBeginYear "  
            "having scsd.selectiveCourse.studyYear = :studyYear AND "  
            "scsd.studentDegree.specialization.degree.id = :degreeId AND "  
            "scsd.studentDegree.active = true")
List<StudentsRegistrationOnCoursesPercent> findPercentStudentsWhoChosenSelectiveCourse(@Param("studyYear") int studyYear,
                                                                                           @Param("degreeId") int degreeId,
                                                                                           @Param("currYear") int currYear);

CodePudding user response：

Try to do it with a native query. Hope that will work.

CodePudding user response：

probably there is some problem with parameter substitution and :currYear - scsd.studentDegree.studentGroup.creationYear scsd.studentDegree.studentGroup.realBeginYear is not recognized as th same "column" in select and group by parts

you could try using group by 1 as in What does SQL clause "GROUP BY 1" mean?