java - Hibernate Criteria division inside sum -
i have 3 tables: route(number, frequency ..), operator(name, ..) , route_operator(route_number, operator_name) . assume have following records in route_operator table +--------------+----------------+ | route_number | operator_name | +--------------+----------------+ | 100 | ok travel | | 111 | ok travel | | 111 | venture travel | +--------------+----------------+ and route table: +--------+-----------+ | number | frequency | +--------+-----------+ | 100 | 4 | | 111 | 4 | +--------+-----------+ now, using hibernate criteria want return sum of routes' frequency operated ok travel but, if there more operators taking route, frequency has divided number of operators take route. thus, returned result must 6 (4 route 100 + 4/2 route 111 because taken venture travel well). i have written raw sql this: select sum(r.frequency / (select count(*) route_operator ro2 ro2.route_number = r.number)) route r inner join