I'm trying to generate a query like this:

SELECT SUM(x.FinalValue)
FROM (
    SELECT i.Id InvoiceId, i.FinalValue
    FROM dbo.Cvw_InvoiceHistory i
    INNER JOIN dbo.CrmObjectExtendedProperty e ON e.CrmObjectId = i.Id
    INNER JOIN dbo.ListPropertyContainer c ON c.OwnerCrmObjectId = i.Id
    LEFT JOIN dbo.VwInvoiceDetail d ON d.ContainerId = c.Id
    LEFT JOIN dbo.VwProduct p ON p.Id = d.ProductId

    WHERE i.CrmObjectTypeId = 'e527179e-a067-485a-b6c7-0c5bf1227a06'

    GROUP BY i.Id, i.FinalValue) x

But it returns invalid result. Because it applies GROUP BY to main query, not inner query.

Here is my C# code:

var predicateBucket = ...;

var fieldExpression = CvwInvoiceHistoryFields.FinalValue * 1M;
var groupBy = new GroupByCollection();
groupBy.Add(CvwInvoiceHistoryFields.Id);
groupBy.Add(CvwInvoiceHistoryFields.FinalValue);

sum = (decimal)DB.GetScalar(CvwInvoiceHistoryFields.FinalValue, null, AggregateFunction.SumDistinct, predicateBucket.PredicateExpression, groupBy, predicateBucket.Relations);

What am I missing?

llblgen pro version?

LLBL Gen Pro v 4.2 using Adapter approach.

You can use QuerySpec.

Example.

var qf = new QueryFactory();

//InnerQuery with the GroupBy

var q1 = qf.Create().Select(OrderFields.CustomerId, OrderFields.Freight).GroupBy(OrderFields.CustomerId, OrderFields.Freight);
q1.Alias = "O";

//OuterQuery with the Summation

var q2 = qf.Create().From(q1).Select(OrderFields.Freight.SetObjectAlias("O").Sum());

using (var adapter = new DataAccessAdapter())
{
    var sum = adapter.FetchScalar<decimal>(q2);
}