[jboss-user] [EJB 3.0] - Group by Date/Timestamp part in EJBQL?

[jagthedrummer]

Hey everybody, I was wondering if there's a way to group by just a part of a date field using EJBQL.  Basically I want to get a report of the total number and total value of all transactions that happened over a year, grouped by month.  I've tried the following query, but it doesn't do the trick.  Does anybody have any ideas?

I have a Transaction Entity with an attribute called transTime which is a java.util.Date.

I tried and failed with this:
"select count(t.id),sum(t.amount),t.transTime.getMonth() from Transaction t group by t.transTime.getMonth()"

I also tried the same thing but with the 'getMonth()' as 'getMoth' just to see what happened.  No luck there either.

I do have it working by just issuing one query for each month over the time period I'm interested in looking at, but I'm hoping to find a way to offload some of that to the DB, especially since I need to be able to genereate these kind of stats at the daily level over different time spans.

Any help would be appreciated.

Thanks,
Jeremy


View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3981438#3981438

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3981438