ORDER BY with GROUP BY query, using JPA and Hibernate

Question

I'm trying to make a query using order by and group by at the same time But I'm taking a following error:

ORA-00979: not a GROUP BY expression

Here is query's code:

  @Query("select distinct p from PointSpreadsheets p left join fetch p.shlSpreadsheetRegistersList srl" +
        "  WHERE (:BUAPP is null OR srl.buApprobation = :BUAPP)" +
        " AND (:STATUS is null OR p.idStatus = :STATUS)" +
        " AND (:CCAPP is null OR srl.ccApprobation = :CCAPP)" +
        " AND (:EMPLOYEE is null OR p.idEmployee = :EMPLOYEE)" +
        " AND (:CCLIST is null or srl.idCostCenter  = :CCLIST)" +
        " AND (p.idEmployee.idBusinessUnit IN (:BU))" +
        " AND (p.referencePeriod.idReferencePeriod IN :PERIODS)"
        + " GROUP BY"
        + " p.buApprobation, p.ccApprobation, p.dateBuApprobation,"
        + " p.dateCcApprobation, p.finalDate, p.idEmployee, p.idPeriodicity,"
        + " p.idSpreadsheet, p.idStatus, p.initialDate, p.referencePeriod"
        + " ORDER BY p.idEmployee")    
public List<PointSpreadsheets> PointSpreadsheetFilter(
        @Param("STATUS") BigDecimal status,
        @Param("BUAPP") BigDecimal buApprobation,
        @Param("CCAPP") BigDecimal ccApprobation,
        @Param("PERIODS") List<BigDecimal> periods,
        @Param("EMPLOYEE") Employees employee,
        @Param("BU") List<BusinessUnits> businessUnits,
        @Param("CCLIST") CostCenter cc);

Try to get just the SQL and run the query. That should help you debug your issue better. Your issue is exactly as mentioned in the error. Take a look at stackoverflow.com/q/7434657/3179169. You need to use some aggregation function or max or min kind of a function to get a result. — clinomaniac
– clinomaniac, Commented Apr 23, 2018 at 18:36
I've tried and it's throwing the same error ORA-00979: not a GROUP BY expression — Baisso Renan
– Baisso Renan, Commented Apr 23, 2018 at 19:00
What did you try? Update the question with the query you tried to run. — clinomaniac
– clinomaniac, Commented Apr 23, 2018 at 19:50
From your query, it appears that you are looking for distinct values of p. Not sure what type column that is but what should it do in case you have multiple values for p in the group you have created in GROUP BY. You need to determine what needs to be done. Should it add those values? Choose the first one? Choose the last one? Average those out if its a numerical value? — clinomaniac
– clinomaniac, Commented Apr 23, 2018 at 19:56

lokori · Accepted Answer · 2018-04-23 19:18:38Z

1

Since there is no aggregate function, like COUNT or something else, the GROUP BY doesn't really mean anything to the database. The poor Database is wondering "group what?" and throws a tantrum. You can certainly have ORDER BY and GROUP BY in the same SQL statement.

answered Apr 23, 2018 at 19:18

lokori

4465 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ORDER BY with GROUP BY query, using JPA and Hibernate

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related