sql - Java HQL Issues with GroupBy/Having Query -

i have make hql using hibernate find out sum of frequencies of museum vigilants museums, museum vigilants occupy same shift per hour - want find mike jones frequency in shift.

this have far:

select v, m.freq/count(m.id) vigilant v inner join v.museums m group m.id having count(m.id) > 0 , v.forename = 'mike jones' 

this me list of objects in hibernate vigilant , number - numbers have gotten correct , wish sum them - i.e. want sum(m.freq/count(m.id)) , return hql hibernate.

i've tried multiple ways, devised sql solution following path , subqueries hql doesn't allow subqueries in clause set me this. have no idea how because adding sum or removing v select statement doesn't work.

any how can work? thanks.

you can't group whole entities:

neither group clause nor order clause can contain arithmetic expressions. hibernate not expand grouped entity, cannot write group cat if properties of cat non-aggregated. have list non-aggregated properties explicitly.

so query becomes this:

select      v.id,     v.prop1     ...,     v.propn,     m.freq/count(m.id) vigilant v  inner join v.museums m      v.forename = 'mike jones' group v.id, v.prop1, .., v.propn 

1. the prop1...propn properties of vigilant (eager fetching complicate further)
2. the having not necessary because have inner join , not vigilant returned if there's no museum
3. the forename filter makes more sense in clause

my advice use simpler query:

select      v.id,     m.freq/count(m.id) vigilant v  inner join v.museums m      v.forename = 'mike jones' group v.id 

and use selected ids retrieve entities second query:

select v  vigilant v  v.id in (:ids)