Aggregate function on collection

snake_net · **Joined:** Sun May 14, 2006 5:20 pm **Posts:** 6

Hi,

I need some help with counting the number of products sold. It is quite easy to get the result with only SQL, but I completly do not know how to do it with HQL.

My tables look as follows:
Products:
Id | InvoiceId | Amount | Price | ...
Invoices
Id | ...

I need to count how many of each product were sold.
I mapped Product as a composite element of Invoice class.

How to write such query?
In pure SQL it would be as follows:

Code:

SELECT [ProductId], Sum([Amount]) as [AmountSold]
FROM [Invoices]
INNER JOIN [Products]
ON [Products].[InvoiceId] = [Invoices].[Id]
GROUP BY [ProductId];

And I think that it would be better for me to get not a mapped object but rather an Array or a list with that values. Is it possible? I am not sure, because CreateSQLQuery has a returnType parameter and according to documentation, it has to be a mapped class.

Is it possible to use sum function on elements function? If not, is it possible to write such query in HQL?

I use NHibernate 1.02 and MS SQL 2005.

Any help would be appreciated. Thanks in advance.

Regards,
snake_net

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Yep, the sum function is available to you. It's been a while since I used the HQL that NHibernate uses, but I think that it'd be something like:

Code:

select p, sum(i.Amount)
from Product p
join p.Invoice as i

You'll get back a list of arrays, in which the first element is a product, and the second is the number sold.

If you don't want the product at all, you can "select p.id ...".