I am facing this error in SQL Server - "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

Question

I am trying to run the below query, but I am encountering this error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

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

I cannot join the tables MC_INVCHG and MC_INVTAX to the main query because it's causing duplicates in the IVCLINE1.ivclinextamt_amt field and the sum ends up being wrong.

Below is sample data I have pulled from the tables by joining them.

Invoice Number	Line	Gross Amount	Tax amount	Change
00 0001180 7 DEC03	1	80	-5.04	NULL
00 0001180 7 DEC03	1	80	5.04	NULL
00 0001180 7 DEC03	2	40	2	NULL

What I am trying to accomplish with the query is, Invoice Line Amount = (80 + (5.04 + (-5.04)) + 0) + (40 + 2 + 0)

Since change is NULL for this, it is being considered as 0.

Is there a way to accomplish this in a different way?

Answer 1

This is a good place to use CROSS APPLY as it allows you to calculate a value and then use it, without the constraints you've run into.

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;

Note square brackets, [], are the best practice way to escape object names.