SQL SUM(Case) Error

Question

    SUM (CASE
                WHEN T6.Currency =
(
    SELECT A0.MainCurncy
    FROM '+@myTempTableName+'.dbo.OADM A0
)
                THEN T6.LineTotal
              else T6.TotalFrgn
            END) as [Mf.Amount],

I got this error:

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

Can anyone help me?

Answer 1

You need to fix the overall query:

SELECT . . .
    SUM(CASE WHEN A0.MainCurncy IS NOT NULL
             THEN T6.LineTotal
             ELSE T6.TotalFrgn
         END) as [Mf.Amount],

FROM . . . LEFT JOIN
     @myTempTableName+'.dbo.OADM A0
     ON A0.MainCurncy = T6.Currency