0

Can you please let me how to execute following query through hibernate?

select count(empid) from (select empid, count(deptid) dept_count from emp group by empid) where dept_count > 1

Basically the query retrieves number of employee which are associated with more than 1 department.

Have created a DetachedCriteria for the subquery with group by function as below:

        DetachedCriteria dc = DetachedCriteria.forClass(Emp.class);
        ProjectionList pl = Projections.projectionList();
        pl.add(Projections.groupProperty("empid"), "empid");
        pl.add(Projections.rowCount(), "dept_count");
        dc.setProjection(pl);

But then not finding a way to count number of employee with dept_count greater than 1.

Shreyansh Shah
  • 1
  • 1
  • please add your detachedCriteria code – Mithat Konuk Jul 11 '16 at 10:36
  • Have added the detached criteria. Thanks! – Shreyansh Shah Jul 11 '16 at 11:15
  • please test this line I did not test it dc.add(Restriction.gt("depth_count","1") ; and also maybe need to create alias for this fields and also check this link : [subquery for detachedcriteria](http://stackoverflow.com/questions/2602490/hibernate-criteria-perform-join-in-subquery-detachedcriteria) and also check this link its also what you want : http://stackoverflow.com/questions/5416540/hibernate-select-groupproperty-rowcount-with-rowcount-n – Mithat Konuk Jul 11 '16 at 12:38
  • Hi Mithat, it's not working. Appreciate if you can provide a working code if possible. – Shreyansh Shah Jul 13 '16 at 05:02
  • Please post all your code i will add working code – Mithat Konuk Jul 13 '16 at 06:36

0 Answers0