I want to execute a complex query in Hibernate, but I am getting errors. I have a product_transaction table that stores all purchases related to a product, basically the transaction history.

I want to pull back all the top selling and top grossing products but the Hibernate SQL keeps throwing errors, I am inexperienced in complex queries and using methods such as criteria etc.

Any suggestions for reproducing these queries (these are in a ProductTransaction.hbm.xml file as a named query)?

select * , sum(amount) as totalsalesamount
from ProductTransaction pt
where pt.ownerUser=:ownerUserId
and pt.purchaseDate between :periodStartDate and :periodEndDate
group by pt.productId
order by totalsalesamount desc
limit :numResults

select *, count(*) sellingcnt
from ProductTransaction pt
where pt.ownerUser=:ownerUserId
and pt.purchaseDate between :periodStartDate and :periodEndDate
group by pt.productId
order by sellingcnt desc
limit :numResults