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 https://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, Apr 23 '18 at 18:36
I've tried and it's throwing the same error ORA-00979: not a GROUP BY expression — Baisso Renan, Apr 23 '18 at 19:00
What did you try? Update the question with the query you tried to run. — clinomaniac, Apr 23 '18 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, Apr 23 '18 at 19:56

score 1 · Accepted Answer · answered Apr 23 '18 at 19:18

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.

ORDER BY with GROUP BY query, using JPA and Hibernate

1 Answers1