How to select multiple columns using sum projection with group by in Hibernate -

my mysql query is:

select      c.first_name,      c.middle_name,      c.last_name,      s.ticker,      count(s.ticker),      sum(t.cumulative_qty),      sum(cumulative_balance) client_master c, security s, transaction_master t  c.id = t. client_master_id , s.id = t.security_id , t.client_master_id = 4  group t.security_id; 

this query returns exact result. want write hibernate criteria has matching result. tried:

criteria criteria = createentitycriteria(transactiondetails.class, "tr")                 .createalias("tr.client", "cl")                 .createalias("tr.security", "se")                 .add(restrictions.eq("cl.id", clientid))                 .setprojection(projections.sum("tr.cumulativeqty"))                 .setprojection(projections.groupproperty("tr.securityid"));  return  criteria.list(); 

but criteria returning list of tr.securityid. want rows. going wrong?

you can not setprojection() multiple times. need create projectionlist first , set each projection. need tell criteria fields need in resultset specifying them in projections.property() .

try this:

projectionlist projlist = projections.projectionlist(); projlist.add(projections.sum("tr.cumulativeqty")); projlist.add(projections.groupproperty("tr.securityid"));  projlist.add(projections.property("firstname")); projlist.add(projections.property("lastname")); //add other fields need in projection list  criteria criteria = createentitycriteria(transactiondetails.class, "tr")                 .createalias("tr.client", "cl")                 .createalias("tr.security", "se")                 .add(restrictions.eq("cl.id", clientid))                 .setprojection(projlist);