2

guys! I'm trying to group query results by timestamp in JPQL using JPA (EclipseLink + Derby) and use them for creating a Result object. My query is:

Select new com.restaurant.entities.Report(o.timeOfOrder, count(o.id), sum(o.price))";
    queryText+=" from Orders o ";
    queryText+="where o.timeOfOrder BETWEEN :start AND :end";
    queryText+=" group by o.timeOfOrder"

Unluckily, the only one millisec will make a huge difference, thus plain group by o.timeOfOrder doesn't work.

I have tried this approach, however I've got 

Internal Exception: java.sql.SQLSyntaxErrorException: Column reference 'ORDERS.TIMEOFORDER' is invalid, or is part of an invalid expression.  For a SELECT list with a GROUP BY, the columns and expressions being selected may only contain valid grouping expressions and valid aggregate expressions.

Perhaps, there are any another way out except changing the type of field in DB?

Community
  • 1
  • 1
ryzhman
  • 674
  • 10
  • 22
  • What time range you want to group? To day, hour, minute? – Robert Niestroj Oct 31 '15 at 18:42
  • According to UserStory, analyst can choose the period for the research. Thereby, I suppose, it means that group by must include set of day, month and year filter simultaneously – ryzhman Oct 31 '15 at 19:06

2 Answers2

2

Your query would have to look something like this: 

Select new com.restaurant.entities.Report(o.timeOfOrder, count(o.id), sum(o.price)) 
from Orders o 
where o.timeOfOrder BETWEEN :start AND :end 
group by year(o.timeOfOrder), month(o.timeOfOrder), day(o.timeOfOrder)

but the year, month and day functions are not jpql standard so you have to use FUNCTION to use your equivalent DB functions to perform what you want. Here's more: https://stackoverflow.com/a/3676865/534877

Community
  • 1
  • 1
Robert Niestroj
  • 15,299
  • 14
  • 76
  • 119
  • Thanks for response! I have tried technik, mentioned by you, however, resultless due to an exception: both FUNC and FUNCTION forms of function invocation throw java.sql.SQLSyntaxErrorException: Column reference 'ORDERS.TIMEOFORDER' is invalid, or is part of an invalid expression. For a SELECT list with a GROUP BY, the columns and expressions being selected may only contain valid grouping expressions and valid aggregate expressions. Weird situation baring in mind that my EclipseLink has 2.5.0.v20130507-3faac2b version :/ – ryzhman Oct 31 '15 at 21:54
0

The issue solves rather simply with a use of FUNCTION('period', fieldToTrim). However GROUPing BY result, you should bear in mind, that 'period' (not fieldToTrim) must (!) be in SELECT part, not only in GROUP BY.

ryzhman
  • 674
  • 10
  • 22