SQL SUM(CASE)语句报错求助：无法对含聚合或子查询的表达式执行聚合

Hey there! Let's break down what's going wrong with your SQL statement and how to fix it quickly.

The Root Issue #

Your original SQL has a subquery nested directly inside the SUM() aggregate function:

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

The error you're hitting:

无法对包含聚合函数或子查询的表达式执行聚合操作。

This happens because SQL Server (judging by the syntax) doesn’t allow subqueries inside an aggregate function’s expression—it can’t properly resolve the execution order when you nest a subquery within SUM().

Simple Fixes #

Since OADM is almost certainly a system config table with only one row for the main currency, we have two straightforward solutions:

Option 1: Store the Main Currency in a Variable First

Pull the main currency value into a variable before running your main query. This removes the subquery entirely from the SUM() calculation:

-- First fetch the main currency into a variable (adjust data type to match your column)
DECLARE @MainCurncy VARCHAR(3)
SET @MainCurncy = (SELECT A0.MainCurncy FROM '+@myTempTableName+'.dbo.OADM A0)

-- Use the variable in your SUM logic
SUM(CASE WHEN T6.Currency = @MainCurncy THEN T6.LineTotal ELSE T6.TotalFrgn END) AS [Mf.Amount]

Option 2: Use a CROSS JOIN to Attach the Main Currency

Since OADM only has one row, a cross join will link the main currency value to every row in your dataset, letting you reference it directly in the CASE statement:

-- Add a CROSS JOIN to OADM in your FROM clause
SUM(CASE WHEN T6.Currency = A0.MainCurncy THEN T6.LineTotal ELSE T6.TotalFrgn END) AS [Mf.Amount]
FROM YourExistingTable T6
CROSS JOIN '+@myTempTableName+'.dbo.OADM A0
-- Keep your other JOINs, WHERE clauses, GROUP BY logic here

Both approaches work by resolving the main currency value before the SUM() aggregation runs, eliminating the nested subquery that triggered the error.

内容的提问来源于stack exchange，提问作者Yavuz Selim Kayış