Hi! I would like to extract more than one count from a single table with different criteria. In SQL I would use scalar subqueries (subqueries inside the "select" statement) or unions, but none of them seems to be supported by Hibernate. Is there an alternative or I'm forced to use several queries or native SQL? Maybe with self-joins?

What I try to do, following the examples in the HQL docs, is something like this: select the name of the cat, the number of this cat's kittens whose weight is over 10 pounds and the number of kittens whose weight is between 7 and 9 pounds.

Thanks in advance

A bit freaky that almost exactly one year after this post, I am trying to do the same thing. Is there anything new with Hibernate 3 that would make this possible? I have gotten it to work with native SQL using "from subqueries," but I know that HQL doesn't support that.

I come close with this rather unorthodox query (assuming two tables Family and Kids):

select family.name, count(distinct all_kids), count(distinct teen)
from Family family
left join family.kids as all_kids
left join family.kids as teen
where teen.age > 12

I get the teen count right, but not all_kids. I have gotten the correct counts with this query (yes, I know it is bad):

select count(distinct all_kids), count(distinct teen)
from Kids as all_kids, Kids as teen
where teen.age > 12

But the performance is just abysmal - tens of seconds for even a very small DB.

Any new thoughts in the past year?