我有以下查询,我想获取我的数据的总计数
SELECT
TI.[text] as zone,
YEAR (ER.Inserted) as [Year],
SUM(CONVERT(INT,DRT.RDRT)) as RDRT,
SUM(CONVERT(INT,DRT.FACT)) as FACT ,
SUM(CONVERT(INT,DRT.ERU)) as ERU,
(
SELECT COUNT(ER1.ReportID)
FROM dbo.EW_Reports ER1
INNER JOIN dbo.EW_Report_InformationManagement ERI ON ER1.ReportID = ERI.ReportID
INNER JOIN EW_Report_Country ERC1 ON ER1.ReportID = ERC1.ReportID
INNER JOIN ApplicationDB.dbo.Country C1 ON ERC1.CountryID = C1.countryId
INNER JOIN ApplicationDB.dbo.Region R1 ON C1.regionId = R1.regionId
INNER JOIN ApplicationDB.dbo.Zone Z1 ON R1.zoneId = Z1.zoneId
WHERE ERI.EmergencyAppeal IS NOT NULL
AND (YEAR ( ER1.Inserted) = YEAR ( ER.Inserted))
AND Z1.zoneId = Z.zoneId
) as emergencyAppeals
FROM EW_Reports ER
INNER JOIN EW_DisasterResponseTools DRT ON ER.ReportID = DRT.ReportID
INNER JOIN EW_Report_Country ERC ON ER.ReportID = ERC.ReportID
INNER JOIN ApplicationDB.dbo.Country C ON ERC.CountryID = c.countryId
INNER JOIN ApplicationDB.dbo.Region R ON c.regionId = R.regionId
INNER JOIN ApplicationDB.dbo.Zone Z ON R.zoneId = Z.zoneId
INNER JOIN ApplicationDB.dbo.Translation T ON Z.translationId = T.translationId
INNER JOIN ApplicationDB.dbo.TranslationItem TI ON T.translationId = TI.translationId
INNER JOIN EW_lofDisasterTypes D ON ER.DisasterTypeID = D.TranslationID AND D.LanguageID = 1 AND TI.languageID = 1
WHERE (YEAR ( ER.Inserted) IN (2011,2012))
GROUP BY TI.[text], YEAR (ER.Inserted)
但是它给下面的错误
在选择列表中,“ ApplicationDB.dbo.Zone.zoneId”列无效,因为它既不包含在聚合函数中,也不包含在GROUP BY子句中。
请协助我解决此错误。
您的表中已经有太多ApplicationDB.dbo.Zone.zoneId记录
简单地按组添加ApplicationDB.dbo.Zone.zoneId即可解决问题
Select ....
.....
GROUP BY TI.[text], YEAR (ER.Inserted) ,ApplicationDB.dbo.Zone.zoneId
对于您的问题,为什么您需要在我的组中添加ApplicationDB.dbo.Zone.zoneId,因为我在您的子查询中使用它,这是因为您在子查询中执行了外部条件
SELECT
----
(
SELECT
-----
INNER JOIN ApplicationDB.dbo.Zone Z1 ON R1.zoneId = Z1.zoneId
WHERE
----
AND Z1.zoneId = Z.zoneId
)
-----
INNER JOIN ApplicationDB.dbo.Zone Z ON R.zoneId = Z.zoneId
WHERE (YEAR ( ER.Inserted) IN (2011,2012))
------
请注意,您的病情在不同的年份
因此您的数据流可能会像这样
ZoneID Years Record
1 2011 1000
1 2012 1000
相同的区域ID包含不同的年份,如果没有适当的分组,则sql无法将Years列分组
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句