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?