Hi all

Emphasize on group by clause, are there alternative to following query (where all cat attributes are explicitly specified)?

catList = db.find("select cat, sum(weight) from cat in class eg.Cat where cat.color='white' group by cat.id, cat.birthdate, cat.color, cat.sex, cat.weight");


Quite a few occasions I am required to return the object itself with the aggregate values and the object can have more than 10 attributes easily.


I also noted that following query work fine in MySQL but not in Oracle as Oracle require every displayed column to be specified in group by clause.

catList = db.find("select cat, sum(weight) from cat in class eg.Cat where cat.color='white' group by cat.sex");

My current alternative is to split it to 2 queries as follow:

result = db.find("select cat.id, sum(weight) from cat in class eg.Cat where cat.color='white' group by cat.sex");

for(Iterator itr=result.iterator(); result.hasNext(); ) {

Object[] row = (Object[]) itr.next();
Cat cat = (Cat) db.load(Cat.class, (String)row[0]);
.
.
.
}

FAQ: http://www.hibernate.org/74.html#A10