Is it possible? I use the GROUP BY clause and select some counts, sums etc. I need to order the list according to these values.

I've tried query in the form
SELECT count(x) FROM t GROUP BY y ORDER BY count(x)
but I've got
java.sql.SQLException: General error: Invalid use of group function

After that I've tried following query:
SELECT count(x) as cnt FROM t GROUP BY y ORDER BY cnt
but I've got
net.sf.hibernate.QueryException: , expected in SELECT

Is there any way how fix up this?

Thanx for any answer...
Martin Stepanek

Yes it is possible, but the queries you have shown are certainly not valid HQL.

Thanx for you reply. I can see the queries don't work, but can you please provide me with the correct HQL for this task? I coudln't find it anywhere in the documentation or on the web.

This is covered in the Hibernate documentation. Please read the "Hibernate Query Language" section.


You might also like to look in the src/net/sf/hibernate/test directory.

ok, there is such an example in the documentation:

select order.id, sum(price.amount), count(item)
from Order as order
join order.lineItems as item
join item.product as product,
Catalog as catalog
join catalog.prices as price
where order.paid = false
and order.customer = :customer
and price.product = product
and catalog.effectiveDate < sysdate
and catalog.effectiveDate >= all (
select cat.effectiveDate
from Catalog as cat
where cat.effectiveDate < sysdate
)
group by order
having sum(price.amount) > :minAmount
order by sum(price.amount) desc

I've composed much simplier query according to this example:

select count(a1.id), sum(a1.total)
from TransactionItemDAO a1
where a1.transaction.typeCode=0
group by a1.serviceKey
order by sum(a1.total) desc

but after all, I get the same exception:
java.sql.SQLException: General error: Invalid use of group function

Is there any solution to this?

Thanks
Martin Stepanek

Why do you want to group by a property you are not selecting? You already grouped with two aggreate functions (count and sum), there is no other value to group by.

Sorry, the query of course should have been:



Anyway, that's not the fundamental problem. The problem is ORDER BY clause cannot directly contain aggregate function (at least MySQL doesn't accept it) and it is possible only in the form of alias - in bare SQL would be something like that (little bit simplified):



This works with SQL, but HQL doesn't allow aliases for retrieved data, so the following query:



throws exception (see the first post). Is there any solution to this problem?

Best regards
Martin Stepanek

I think someone already took this issue to the MySQL team and they were considering fixing MySQL ... not sure what came out of this tho'.

Hi

I am having problem with group by.
I saw the below example in the docs.

select cat.color, sum(cat.weight), count(cat)
from Cat cat group by cat.color

Does the queryobject return Cat objects with sum and count as well? How do we loop..When I loop and typecast to Cat, it gives classcast exception.

I am using

<property name="noOfCats" update="false" insert="false" formula="select count(b.cat) from CAT b where b.COLOR = COLOR"/>

Here CAT is DB table name and COLOR the db column name.

Is this correct? I am getting

I have added getters and setters for "noOfCats" in my java class.

Is there a complete example with mapping file and java code showing this?