HQL - select count from select

Question

I would like to be able to count the amount of records returned in another query. Using SQL it's easy:

select count (*) 
from  (
  select count(first_name) 
  from users 
  group by first_name) as temp

Does anyone have any idea how can I do that using HQL?

Answer 1

I think it is not possible in a generic way. HQL does - as far as I know - not support subqueries in the from clause. (To be sure, try it. There are sometimes hidden features...)

In this case, the query would be:

select count(distinct first_name)
from users 

It seems not possible to count multiple distinct columns.

There is a feature for QueryOver (I don't know if it exists and how it would be called in Java).

If you desperately need it for HQL, you could write such a method and send it to the community ...

Answer 2

I hope it will help someone. This is a valid HQL/SQL query:

SELECT COUNT(*) FROM users AS u WHERE u.id IN 
  (SELECT u2.id FROM users AS u2 GROUP BY u2.first_name);

Selecting IDs of grouped rows and then count the rows with id in this list.

UPD: Performance of this query is very low.

Answer 3

Unless I got your question wrong, if the intent is only to find the number of records returned by the query, you can try the below version:

 session.createQuery("select count(first_name) from Users
 group by first_name)").list().size();

Note that, the Users in the above query is the Entity name not the Table name.

In case if you are looking for HQL query syntax that is similar to your SQL query (i.e., subselect in from clause), it is not supported by Hibernate HQL as per HHH-3356

Answer 4

select count(*) from
(
   select AUTO_ID_CUSTOMER, sum(UNIT_QTY*UNIT_PRICE)
   from V_TRANSACTIONS
   where INV_DATE > '01/01/2006'
   group by AUTO_ID_CUSTOMER
   having sum(UNIT_QTY*UNIT_PRICE) < 100000
)