I am searching for a way to count the number of rows that would be returned by a query with a group by clause.

For example a count(*) around this query:
select NAME, count(AGE) from PERSON where NAME like :nameParam group by NAME

This does not work (SQL semantics):
select count(*) from PERSON where NAME like :nameParam group by NAME

In SQL I could use a subquery in the from clause:
select count(*) from (select NAME, count(AGE) from PERSON where NAME like :nameParam group by NAME)

Hibernate does not support sub queries in the from clause. Is there a possibility to get the number of rows that would be returned by the first query?


--------------------------------------------------------------------------------

This query is correct.
What is your database ?

Thank you for the answer. Sorry, I chose a wrong example. This is a correct example (I replaced the count(AGE) with sum(AGE)): I group according to name and then sum the ages of all persons in a group.

[img]select%20NAME,%20sum(AGE)%20from%20PERSON%20where%20NAME%20like%20:nameParam%20group%20by%20NAME[/img]

I now need a way to calculate the number of rows that would be returned by the above query. But the following query counts how many items belong to each group.

[img]select%20count(*)%20from%20PERSON%20where%20NAME%20like%20:nameParam%20group%20by%20NAME[/img]

The solution in SQL would be:

[img]select%20count(*)%20from%20(select%20NAME,%20sum(AGE)%20from%20PERSON%20where%20NAME%20like%20:nameParam%20group%20by%20NAME)[/img]

You can't do this with Hibernate because it does not support subqueries in the from clause. Am I right? And is there a solution with Hibernate?

Best Regards,
Oliver

One further addition: I need a general solution that works in cases where more than one attributes are specified in the group by clause.

try

select count(distinct name) from person where name like ...

regards

Well, you are right. This is the solution for my example above. But unfortunately, as I have written in my last post, I need a general solution that works too if there are more than one grouping attributes:


(String name;
String city;
int age;)

Your statement does not work here because count() allows only one parameter.

My question remains: Is there any way to count the number of rows that would be returned by this query without actually executing it? (That is to use the count() function somehow or any other way).

This all would be no problem if Hibernate would support subqueries in the from clause - a really missing feature in my opinion.

I hope that my example is complete now. In my real query, there are more than one aggregate functions in the select clause, but I really hope that this makes no difference. To tell the whole story: I use such a query in an application that uses paging (implemented using the Hibernate paging functionalities, which work fine). In addition to displaying one page of results, I have to display the total number of hits. But I did not find any way to do this without executing the complete (none-paged) query. By the way: We use Oracle 9i which implements the paging quite good.

I use next :

String query = ...;
Query q = session.createQuery(query);
q.setParameter(...);
ScrollableResults scr = q.scroll();
scr.last();
size = scr.getRowNumber();

regards

That looks interesting.

Thanks a lot!

... but is not always a good solution. There exist some DBMS (like hsqldb) which suport cursors only in memory: it loads the entire resultset to memory, resulting in an out of memory exception.

What would be the alternative in this case ?

I'm facing the problem that we need to support both MySQL and MSSQL, they both support subqueries in the from clause, but not Hibernate ...

- Pascal

Did you try using a native SQL query? session.createSQLQuery("...")

Yes, this is what we are currently doing, but it's sad to see that we were 100% hql before, and now, 98% ...

We though that there was maybe another solution that we were not aware of.

It's more about principles.

- Pascal