Aggregate Function Query, Mapping, Code Example

rejimani · **Joined:** Fri May 23, 2008 5:39 am **Posts:** 1

The example query criteria code for the aggregate function usage from the nhibernate documentation.

Code:

IList results = session.CreateCriteria(typeof(Cat))
.SetProjection( Projections.ProjectionList()
.Add( Projections.RowCount(), "catCountByColor" )
.Add( Projections.Avg("Weight"), "avgWeight" )
.Add( Projections.Max("Weight"), "maxWeight" )
.Add( Projections.GroupProperty("Color"), "color" )
)
.AddOrder( Order.Desc("catCountByColor") )
.AddOrder( Order.Desc("avgWeight") )
.List();

My question is, Do we need to define the fields avgWeight, maxWeight etc in the mapping and class files?

Any full example code available?

I need to create the query for the following code

Code:

SELECT 
   TierId as NETWORK, 
   TierTypeId as TIER, 
   SUM(ApportionedCallCount) as COUNT, 
   SUM(Duration)/60 as MINUTES, 
   RateUsed as RATE,
   SUM(RatingElementCost) as CHARGE, 
   SUM(RatingElementCost) * 0.05 VAT, 
   (SUM(RatingElementCost) + SUM(RatingElementCost) * 0.05) TOTAL
FROM CDR
WHERE CallDate = '14-apr-2008'
   AND CallDirection = 'I'
   AND RatingElementId = 'TRA'
   AND IsDeleted=0
   AND NetworkOperatorId = 'MUL'
   AND Duration > 0
GROUP BY TierId, TierTypeId, RateUsed

wolli · **Posted:** Fri May 23, 2008 6:56 am

No, that's just an alias for the calculated column. If you don't specify this alias, one is created for you.

There are examples in the test cases. Have a look at the source distribution.