NHibernate HQL subquery problem

Question

My (simplified) domain model contains a many-to-many relationship called Ownership between Customer and Product. I would like to write an NHibernate HQL query that tallies the number of customers who own each product (under certain criteria). Note that a customer can own a given product multiple times, but this should only count as one "vote" for that product.

The SQL for this is easy enough:

select ProductID, count(*)
from (
    select distinct CustomerID, ProductID
    from Ownership
    where ...
) tbl
group by ProductID

Unfortunately, HQL does not support subqueries in the from clause. How can I express this query in HQL?

Answer 1

select ProductID, count(distinct CustomerID)
from Ownership
group by ProductID

Credit to Jason Meckley