不清楚您的问题是什么。但是,在group by语句中,所有列都必须在查询的表中或派生自查询的表。因此,您不能在group by中使用divisionname。而且你也不需要它。 因此,即时的语法修复方案是:
select AccountCode , 'All Companies' as divisionName , AcName ,
LF_AccountType , Sum(ISNULL(runningBalance,0)) as runningBalance
from Table
group by AccountCode, AcName , LF_AccountType
由于您正在使用固定值,只需使用聚合函数并将其从GROUP BY
子句中删除即可,这是没有意义的。例如:
SELECT AccountCode , AcName , LF_AccountType ,
MIN('All Companies') as divisionName,
SUM(ISNULL(runningBalance,0)) as runningBalance from Table
group by AccountCode , AcName , LF_AccountType
尝试使用带子查询的Windows函数:
SELECT * FROM (
Select AccountCode , 'All Companies' as divisionName , AcName , LF_AccountType ,
Sum(ISNULL(runningBalance,0)) OVER (PARTITION BY LF_AccountType ) as runningBalance ,
ROW_NUMBER() OVER(PARTITION BY LF_AccountType) as rn
from Table
) AS T1 WHERE T1.rn = 1
或者使用带有 Group by 的子查询
Select 'All Companies' as divisionName, * From (
select AccountCode , AcName ,
LF_AccountType , Sum(ISNULL(runningBalance,0)) as runningBalance
from Table
group by AccountCode, AcName , LF_AccountType
) T1
AcName
是否可能因AccountCode
而具有不同的空格?也许尝试修剪该列? - fqhv