I know there have been posts around this before but it seems alternatives have always been suggested!

I need to group some fields but I need to perform a function on one of those fields. The SQL I've used is:

SELECT   FriendlyReference, GBPPerCharge, Description, COUNT(*) AS NumItems, SUM(GBPPerCharge) AS GGPTotal, COALESCE (SUM(AlreadyOnPayments), 0) 
                      AS AmountPaid
FROM         (SELECT     Reservation.FriendlyReference, dbo.API_UFN_GetGBPChargeTotal(ChargeDetails.ChargeID) AS GBPPerCharge, 
                                              ChargeDetails.Description, SUM(PaymentCharge.GBPPortionOfCharge) AS AlreadyOnPayments
                       FROM       Reservation INNER JOIN
                                              ChargeDetails ON Reservation.ReservationID = ChargeDetails.ReservationID LEFT OUTER JOIN
                                              PaymentCharge ON ChargeDetails.ChargeID = PaymentCharge.ChargeID
                       WHERE      (Reservation.ProjectID = 17)
                       GROUP BY Reservation.FriendlyReference, ChargeDetails.Description, ChargeDetails.ChargeID) DERIVEDTBL
GROUP BY FriendlyReference, GBPPerCharge, Description

I've got the DerivedTableDefinition set with the code:

Dim dtFields As New ResultsetFields(4)
dtFields.DefineField(ReservationFields.FriendlyReference, 0)
dtFields.DefineField(ChargeDetailsFields.ChargeId, 1, "GBPPerCharge")
dtFields(1).ExpressionToApply = New DbFunctionCall( _
    "dbo.API_UFN_GetGBPChargeTotal({0})", _
    New Object() {ChargeDetailsFields.ChargeId})
dtFields.DefineField(ChargeDetailsFields.Description, 2)
dtFields.DefineField(PaymentChargeFields.GbpportionOfCharge, 3, "AlreadyOnPayments", AggregateFunction.Sum)
Dim dtGroupBy As New GroupByCollection(dtFields(0))
dtGroupBy.Add(dtFields(2))
dtGroupBy.Add(ChargeDetailsFields.ChargeId)
Dim dtFilter As IPredicateExpression = New PredicateExpression(ReservationFields.ProjectId = mProject.ProjectId)
Dim dtDefinition As New DerivedTableDefinition(dtFields, "UnGroupedCharges", dtFilter, dtGroupBy)

I just don't really know where to go from here. I've seen posts suggesting doing things like "Set the left operand of a dynamic relation to the derivedtable definition, and leave the right operand as null." but that doesn't tell me how to get the fields defined to go into my Charges datatable.

What do I need to change to define my fields and do I have to add anything else to the code below?:

Dim MainFields As New ResultsetFields(6)
MainFields.DefineField(DerivedTableDefinition.fields("FriendlyReference"), 0)
MainFields.DefineField(DerivedTableDefinition.fields("GBPPerCharge"), 1)
MainFields.DefineField(DerivedTableDefinition.fields("Description"), 2)
MainFields.DefineField(DerivedTableDefinition.fields(""), 3, "NumItems", AggregateFunction.Count)
MainFields.DefineField(DerivedTableDefinition.fields("GBPPerCharge"), 4, "GGPTotal", AggregateFunction.Sum)
MainFields.DefineField(DerivedTableDefinition.fields("AlreadyOnPayments"), 5, "AmountPaid", AggregateFunction.Sum)

Dim MainGroupBy As New GroupByCollection(MainFields(0))
MainGroupBy.Add(MainFields(1))
MainGroupBy.Add(MainFields(2))

MyDao.GetMultiAsDataTable(MainFields, Charges, 0, Nothing, Nothing, Nothing, False, MainGroupBy, Nothing, 0, 0)

VB.NET 2008 / .NET 2.0, LLBLGen 2.6, Self Servicing

Hi there,

Here is a tiny example that shows how to do that (using Northwind DB):

SELECT 
    [LPA_O1].[OrderId], 
    COUNT([LPA_O1].[Total]) AS [Global] 

FROM 
    (
        SELECT 
            [Northwind].[dbo].[Order Details].[OrderID] AS [OrderId], 
            [Northwind].[dbo].[Order Details].[ProductID] AS [ProductId], 
            SUM([Northwind].[dbo].[Order Details].[Quantity] * [Northwind].[dbo].[Order Details].[UnitPrice]) AS [Total] 
        FROM [Northwind].[dbo].[Order Details]  
        GROUP BY [Northwind].[dbo].[Order Details].[OrderID]) [LPA_O1] 
        
GROUP BY [LPA_O1].[OrderId]

// set the subquery
ResultsetFields subqueryFields = new ResultsetFields(3);
subqueryFields.DefineField(OrderDetailFields.OrderId, 0);
subqueryFields.DefineField(OrderDetailFields.ProductId, 1);
subqueryFields.DefineField(new EntityField("Total",
             (OrderDetailFields.Quantity * OrderDetailFields.UnitPrice), AggregateFunction.Sum), 2);

// set the groupBy of the subquery
GroupByCollection subqueryGroupBy = new GroupByCollection();
subqueryGroupBy.Add(subqueryFields[0]);
subqueryGroupBy.Add(subqueryFields[1]);

// create the DerivedTableDefinition
DerivedTableDefinition dtDefinition = new DerivedTableDefinition(subqueryFields, "OrderDetailTotals", null, subqueryGroupBy);

// in the DynamicRelation set null in the right operand (there is no join)
DynamicRelation relation = new DynamicRelation(dtDefinition, JoinHint.Inner,
                   null, null);

// relations of the main query
RelationCollection relations = new RelationCollection();
relations.Add(relation);

// setup the main query fields
ResultsetFields orderFields = new ResultsetFields(2);           
orderFields.DefineField(new EntityField("OrderId", "OrderDetailTotals", typeof(int)), 0);
orderFields.DefineField(new EntityField("Total", "OrderDetailTotals", typeof(int)), 1, "Global", AggregateFunction.Count);

// setup the main groupBy clause
GroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(orderFields[0]);

// fetch
DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(orderFields, dynamicList, 0, null, null, relations, true, groupByClause, null, 0, 0);

As for the custom DB Function, please use DBFunctionCalls

Perfect. Thanks.

For completeness, and if anybody wants to see a VB example, my code is now:

Dim dtFields As New ResultsetFields(5)
dtFields.DefineField(ReservationFields.FriendlyReference, 0)
dtFields.DefineField(ChargeDetailsFields.ChargeId, 1, "GBPPerCharge")
dtFields(1).ExpressionToApply = New DbFunctionCall( _
    "dbo.API_UFN_GetGBPChargeTotal({0})", _
    New Object() {ChargeDetailsFields.ChargeId})
dtFields.DefineField(ChargeDetailsFields.Description, 2)
dtFields.DefineField(PaymentChargeFields.GbpportionOfCharge, 3, "AlreadyOnPayments")
dtFields(3).ExpressionToApply = New DbFunctionCall( _
    "COALESCE(SUM({0}),0)", _
    New Object() {PaymentChargeFields.GbpportionOfCharge})
dtFields.DefineField(ChargeDetailsFields.ChargeId, 4)

Dim dtGroupBy As New GroupByCollection(dtFields(0))
dtGroupBy.Add(dtFields(2))
dtGroupBy.Add(ChargeDetailsFields.ChargeId)

Dim dtRelations As IRelationCollection = New RelationCollection
dtRelations.Add(ReservationEntity.Relations.ChargeDetailsEntityUsingReservationId)
dtRelations.Add(ChargeDetailsEntity.Relations.PaymentChargeEntityUsingChargeId, JoinHint.Left)

Dim dtFilter As IPredicateExpression = New PredicateExpression(ReservationFields.ProjectId = mProject.ProjectId)
Dim dtDefinition As New DerivedTableDefinition(dtFields, "UnGroupedCharges", dtFilter, dtRelations, dtGroupBy)

Dim MainFields As New ResultsetFields(6)
MainFields.DefineField(New EntityField("FriendlyReference", "UnGroupedCharges", GetType(String)), 0)
MainFields.DefineField(New EntityField("GBPPerCharge", "UnGroupedCharges", GetType(Decimal)), 1)
MainFields.DefineField(New EntityField("Description", "UnGroupedCharges", GetType(String)), 2)
MainFields.DefineField(New EntityField("GBPPerCharge", "UnGroupedCharges", GetType(Decimal)), 3, "NumItems", AggregateFunction.Count)
MainFields.DefineField(New EntityField("GBPPerCharge", "UnGroupedCharges", GetType(Decimal)), 4, "GBPTotal", AggregateFunction.Sum)
MainFields.DefineField(New EntityField("AlreadyOnPayments", "UnGroupedCharges", GetType(Decimal)), 5, "AmountPaid", AggregateFunction.Sum)

Dim MainGroupBy As New GroupByCollection(MainFields(0))
MainGroupBy.Add(MainFields(1))
MainGroupBy.Add(MainFields(2))

Dim MainRelation As New DynamicRelation(dtDefinition, JoinHint.Inner, Nothing, Nothing)
Dim MainRelationCollection As IRelationCollection = New RelationCollection
MainRelationCollection.Add(MainRelation)

Dim Charges As New DataTable
Dim MyDao As New TypedListDAO
MyDao.GetMultiAsDataTable(MainFields, Charges, 0, Nothing, Nothing, MainRelationCollection, False, MainGroupBy, Nothing, 0, 0)

And this generated the following SQL:

SELECT DISTINCT 
                      FriendlyReference, GBPPerCharge, Description, COUNT(GBPPerCharge) AS NumItems, SUM(GBPPerCharge) AS GBPTotal, SUM(AlreadyOnPayments) 
                      AS AmountPaid
FROM         (SELECT     [Reservation].[FriendlyReference], 
                                              dbo.API_UFN_GetGBPChargeTotal([dbo].[ChargeDetails].[ChargeID]) AS [GBPPerCharge], 
                                              [dbo].[ChargeDetails].[Description], COALESCE (SUM([dbo].[PaymentCharge].[GBPPortionOfCharge]), 0) 
                                              AS [AlreadyOnPayments], [dbo].[ChargeDetails].[ChargeID] AS [ChargeId]
                       FROM       (([Reservation] INNER JOIN
                                              [dbo].[ChargeDetails] ON 
                                              [Reservation].[ReservationID] = [dbo].[ChargeDetails].[ReservationID]) LEFT JOIN
                                              [dbo].[PaymentCharge] ON [dbo].[ChargeDetails].[ChargeID] = [dbo].[PaymentCharge].[ChargeID])
                       WHERE      ([Reservation].[ProjectID] = 17)
                       GROUP BY [Reservation].[FriendlyReference], [dbo].[ChargeDetails].[Description], 
                                              [dbo].[ChargeDetails].[ChargeID]) LPA_U1
GROUP BY FriendlyReference, GBPPerCharge, Description