This SQL works but I just can't work out how to reproduce it in Queryspec

SELECT
    fd.ID,
    (at.Amount + COALESCE(at.VATAmount, 0)) AS Amount,
    COALESCE(x.Amount, 0) AS AllocatedAmount
FROM FeeDocument fd JOIN AccountsTransaction at ON at.ID = fd.ID
LEFT JOIN
(
    SELECT
        a.TargetAccountsTransactionID,
        SUM((at.Amount + COALESCE(at.VATAmount, 0))) AS Amount
    FROM
    Allocation a JOIN AccountsTransaction at ON at.ID = a.ID
    GROUP BY a.TargetAccountsTransactionID
) x ON x.TargetAccountsTransactionID = fd.ID

(the FROM and JOINs on the same line indicate inheritance)

I won't even show the crap I've come up with trying it but I did want to try to keep the 'inner query' as a query in its own right if possible since it is reusable in other similar queries.

Cheers Simon

            var qf = new QueryFactory();

            var allocationSummaryQuery = qf.Create()
                .Where(AllocationFields.TargetAccountsTransactionID != DBNull.Value)
                .GroupBy(AllocationFields.TargetAccountsTransactionID)
                .Select(AllocationFields.TargetAccountsTransactionID,
                    (AllocationFields.Amount + AllocationFields.VATAmount.CoalesceToZero()).Sum().As("AA")
                );

            var query = qf.Create()
                .From(qf.FeeDocument
                    .LeftJoin(allocationSummaryQuery.As("X"))
                    .On(FeeDocumentFields.ID == AllocationFields.TargetAccountsTransactionID.SetObjectAlias("X")))
                .Where(FeeDocumentFields.FeeDocumentType == FeeDocumentTypes.Invoice.Value)
                .Select(() => new XAllocInfo
                                  {
                                      ID = FeeDocumentFields.ID.ToValue<int>(),
                                      GrossAmount = FeeDocumentFields.Amount.ToValue<decimal>() + (FeeDocumentFields.VATAmount.ToValue<decimal?>() ?? 0m),
                                      AllocatedAmount = AllocationFields.Amount.SetObjectAlias("X").As("AA").ToValue<decimal?>() ?? 0m
                                  });

This seems to work! Is it the best way?

That's how I would have done it.