Hi
I am trying to run some native SQL using CreateSQLQuery. The issue here is that I am performing an aggregate function on the sub table (in the FROM clause) that is also doing an aggregate.
Here is the SQL I am trying to run:
Code:
SELECT AVG(sub.profileCount)
FROM (SELECT COUNT(pv.ProfileId) AS profileCount
FROM ProfileView pv
GROUP BY pv.ProfileId) AS sub
This is doing a COUNT on ProfileID of ProfileView and then doing an AVG on that result.
When I use:
Code:
IQuery qry = ActiveSession.CreateSQLQuery(sql);
int i = Int32.Parse(qry.UniqueResult<double>().ToString());
I get the following error:
Code:
Return types of SQL query were not specified [*MY SQL STATEMENT*]
Using
Code:
int i = Int32.Parse(qry.List()[0].ToString());
Gives the same error. So I thought of doing a .AddEntity(typeof(double)), but errors as well with the following:
Code:
No persister for: System.Double
So, does anybody have an idea on how to get the result?
Cheers :)
Edit: I am using SQL 2005