How write SQL sum() and groupby in hibernate?

Question

My query is :

select GRN_NO, IDENTIFICATION_NO, RBI_TRN_DATE, PERIOD_FROM, PERIOD_TO,
       SUM(AMOUNT) AS AMOUNT
from PTAX_ECHALLAN_DATA
where IDENTIFICATION_NO in ('ECC0013056', '192009028150', '20150086699') 
GROUP BY GRN_NO;

I am try it with this code :

Criteria cr = session.createCriteria(PTAX_ECHALLAN_DATA.class);
cr.setProjection(Projections.projectionList()
.add(Projections.sum("amount").as("amount"))
.add(Projections.property("rbiTrnDate").as("rbiTrnDate"))
.add(Projections.property("idNo").as("idNo"))
.add(Projections.property("grnDate").as("grnDate"))
.add(Projections.property("preiodTo").as("preiodTo"))
 .add(Projections.property("preiodFrm").as("preiodFrm"))
 .add(Projections.groupProperty("id.grnNO")));
 cr.add(Restrictions.in("idNo", Arrays.asList(OldEc, EC, applnNo)));
cr.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
List list = cr.list();

But get error.

The error is :"Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression".

how do I write this? Please help. thanks in advance.

Answer 1

You need to select all other coulmns except aggregated column in group by

select GRN_NO, IDENTIFICATION_NO, RBI_TRN_DATE, PERIOD_FROM, PERIOD_TO,
       SUM(AMOUNT) AS AMOUNT
from PTAX_ECHALLAN_DATA
where IDENTIFICATION_NO in ('ECC0013056', '192009028150', '20150086699') 
GROUP BY GRN_NO, IDENTIFICATION_NO, RBI_TRN_DATE, PERIOD_FROM, PERIOD_TO