Unable to group after left join

Question

Here is my query (from a trigger):

UPDATE QuoteItemsGroupFeature
SET Cost = (QuoteItemsGroup.BaseCost + QuoteItemsGroup.AccumulatedCost + 
    ISNULL(SUM(ParentQuoteItemsGroupFeature.Cost), 0)) * INSERTED.Amount
FROM QuoteItemsGroupFeature INNER JOIN INSERTED
    ON QuoteItemsGroupFeature.QuoteItemsGroupFeatureId = 
    INSERTED.QuoteItemsGroupFeatureId
INNER JOIN QuoteItemsGroup ON QuoteItemsGroup.QuoteItemsGroupId = 
    INSERTED.QuoteItemsGroupId
LEFT OUTER JOIN QuoteItemsGroupFeature ParentQuoteItemsGroupFeature ON 
    ParentQuoteItemsGroupFeature.QuoteItemsGroupFeatureId = 
    INSERTED.ParentQuoteItemsGroupFeatureId
GROUP BY QuoteItemsGroup.BaseCost, 
    QuoteItemsGroup.AccumulatedCost, 
    INSERTED.Feature, 
    INSERTED.Cost

It says:

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'GROUP'.

Answer 1

You have to remove the aggregate to a derived table (or CTE)

I think this is what you want, and I think your group by is wrong too. I've also added aliases to make it easier to read (for me anyway)

UPDATE
    QF
SET
    Cost = foo.Cost
FROM
    QuoteItemsGroupFeature QF JOIN 
    (
    SELECT
        I.QuoteItemsGroupFeatureId,
        (Q.BaseCost + Q.AccumulatedCost + ISNULL(SUM(P.Cost), 0)) * I.Amount 
            AS Cost
    FROM
        INSERTED I
        JOIN
        QuoteItemsGroup Q ON Q.QuoteItemsGroupId = I.QuoteItemsGroupId
        LEFT JOIN
        QuoteItemsGroupFeature P ON 
            P.QuoteItemsGroupFeatureId = I.ParentQuoteItemsGroupFeatureId
    GROUP BY
        Q.BaseCost, 
        Q.AccumulatedCost, 
        --I.Feature, ??
        --I.Cost, ??
        I.Amount
    ) foo ON QF.QuoteItemsGroupFeatureId = foo.QuoteItemsGroupFeatureId