Count the number of groups per row in sleep mode with criteria

Question

I want to count the number of group by rows with the hibernate Criteria API, but I can only count the number of rows aggregated in each group:

 ProjectionList projectionList = Projections.projectionList() .add(Projections.groupProperty("color")) .add(Projections.rowCount()); Criteria criteria = session.createCriteria("ProductEntity"); criteria.setProjection(projectionList); // adding some criteria List results = criteria.list(); 

In the code above, the code is:

 select p.color, count(*) from product p group by p.color 

But I want this query:

 select count(*) from (select p.color from product p group by p.color) 

I know that this is possible using HQL, but I do not want to use it. So how can I do this using the criteria API?

Answer 1

If you want to know how many different colors are present, you should use

 Projections.countDistinct("color") 

This will result in a query that returns the same result:

 select count(*) from (select p.color from product p group by p.color)