我在SQL服务器中遇到此错误 - "Cannot perform an aggregate function on an expression containing an aggregate or a

这是使用CROSS APPLY的好地方，因为它允许您计算一个值，然后使用它，而不受您遇到的约束。

SELECT 
IVCSUM1.ivcnum [Invoice Number]
, IVCSUM1.ivcgrsamt_amt [Gross Amount]
, IVCSUM1.ivcgrsamt_cur [Gross Amount Currency]
, SUM(ISNULL(IVCLINE1.ivclinextamt_amt, 0) 
+ ISNULL(S1.TaxAmount_amt_Total, 0) 
+ ISNULL(S2.amtchg_amt_Total, 0)) [Invoice Line Amount]
FROM MC_IVCSUM IVCSUM1
LEFT JOIN MC_IVCLINE IVCLINE1 on IVCSUM1.ivsmoi = IVCLINE1.invsumm_oi
CROSS APPLY (
SELECT SUM(TaxAmount_amt) TaxAmount_amt_Total
FROM MC_INVTAX
WHERE ivclinref_oi = IVCLINE1.ivlnoi
) S1
CROSS APPLY (
SELECT SUM(amtchg_amt) amtchg_amt_Total
FROM MC_INVCHG INVCHG1
WHERE IVCLINE1.ivlnoi = ivclinref_oi
) S2
GROUP BY ivsmoi, ivcnum, IVCSUM1.ivcgrsamt_amt, IVCSUM1.ivcgrsamt_cur;

注意方括号[]是转义对象名称的最佳实践方法。