Hi,
I have a problem using the aggregation function SUM.
In particular, I have the following table:



and the database is SQLServer 2000.
I want to calculate the SUM of TonnageValue, so I wrote by HQL a very simple query:

IQuery q = _session.CreateQuery("SELECT sum(TonnageValue) from TrackSectionTonnage");
IList list = q.List();

list contains 3 items (the number of total items in the table) because the query produced by NHibernate is:

select tracksecti0_.Id as Id, tracksecti0_.CreateUser as CreateUser,
tracksecti0_.UpdateTimestamp as UpdateT10_, tracksecti0_.DateFrom as DateFrom, tracksecti0_.UpdateUser as UpdateUser, tracksecti0_.Guid as Guid, tracksecti0_.CreateTimestamp as CreateTi3_, tracksecti0_.TonnageValue as TonnageV9_, tracksecti0_.DateTo as DateTo, tracksecti0_.Duration as Duration,
tracksecti0_.IdTrackSection as IdTrack12_, tracksecti0_.RowVersion as RowVersion from dbo.Tonnage_TrackSectionTonnage tracksecti0_

I cannot understand why my sum operator is ignored.

Antonella

Hibernate version: 1.0.3

Mapping documents:
<class name="ObjectModel.Tonnage.TrackSectionTonnage, ObjectModel" table="Tonnage_TrackSectionTonnage">
<id name="Id" type="Int32" column="Id" access="field.pascalcase-m-underscore">
<generator class="hilo">
<param name="table">NH_Key_Tonnage</param>
<param name="column">Tonnage_TrackSectionTonnage</param>
<param name="max_lo">0</param>
</generator>
</id>
<version name="RowVersion" column="RowVersion" type="Int32" unsaved-value="negative" />
<property name="CreateTimestamp" column="CreateTimestamp" type="DateTime" />
<property name="CreateUser" column="CreateUser" type="Int32" />
<property name="DateFrom" column="DateFrom" type="DateTime" />
<property name="DateTo" column="DateTo" type="DateTime" />
<property name="Duration" column="Duration" type="Int16" />
<property name="Guid" column="Guid" type="Guid" />
<property name="TonnageValue" column="TonnageValue" type="Decimal" />
<property name="UpdateTimestamp" column="UpdateTimestamp" type="DateTime" />
<property name="UpdateUser" column="UpdateUser" type="Int32" />
<many-to-one name="TrackSection" column="IdTrackSection" class="ObjectModel.Asset.TrackSection, ObjectModel" cascade="none" />
</class>[/code]

Have you try to make a group by ?

No, I haven't, because I should make a Group By like this:

group by Id, CreateUser, UpdateTimestamp, DateFrom, UpdateUser, Guid, CreateTimestamp, TonnageValue, DateTo, Duration, IdTrackSection, RowVersion

whitch added at the end of my query I think will return three rows too because the Id is a unique field.

Any other idea?

Antonella

I know that it isn't an answer but have you try Criterion ?
There is also aggregation function ...

Have you try to use alias ?
IQuery q = _session.CreateQuery("SELECT sum(tst.TonnageValue) from TrackSectionTonnage as tst");
IList list = q.List();

Is "TonnageValue" the correct hibernate property name ?

Hi, thank you for the response.


Yes, I know Criterion, I'm using it in another context (without Sum), but in this particular case I need to compose the hql query piece by piece, so I would like to use CreateQuery or Find.


Yes, I used alias with the same result...


Yes, of course. I haven't exceptions... I have a list of 3 items, each one containing the correct single value of TonnageValue, but not the sum.

Thanks
Antonella