Wouldn't you group by f or select by f.lastResp ?
JPA 2 is a pain because it forces us to only group by stuff in the select clause, some DBA propeller head negotiate that stupid rule into the spec. Chris -----Original Message----- From: Joel Halbert [mailto:[email protected]] Sent: Wednesday, 24 November 2010 1:14 AM To: [email protected] Subject: count and 'group by' in jpql Hi All, A group by query along the following lines works as I expect in native SQL: select *,count(*) from FRONTIER group by LAST_RESP; Given the following data in FRONTIER ------------------------------ COL1 | COL2 | LAST_RESP ------------------------------ a | b | 200 c | d | 200 d | e | 400 ------------------------------- I get the results: a,b,200, 2 d,e,400, 1 If i try the same in JPQL: Query q = em.createQuery("select f, count(f) from GlobalFrontier f group by f.lastResp", GlobalFrontier.class); I get the following exception: Exception in thread "main" <openjpa-2.0.1-r422266:989424 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: Your query on type "class com.su3analytics.crawler.model.GlobalFrontier" with filter "select f, count(f) from GlobalFrontier f group by f.lastResp" is invalid. Your select and having clauses must only include aggregates or values that also appear in your grouping clause. at org.apache.openjpa.kernel.ExpressionStoreQuery$AbstractExpressionExecutor$Va lidateGroupingExpressionVisitor.enter(ExpressionStoreQuery.java:533) Is this a correct? Thanks, Joel
