时间戳

首页 > 解决方案 > SQL Server：Sum 函数返回错误值

sql-server - SQL Server：Sum 函数返回错误值

问题描述

所以这是我的查询

   select a.ERSDataValues_ERSCommodity_ID,c.ersGeographyDimension_country,
   b.ERSTimeDimension_Year,
  sum(a.ERSDataValues_AttributeValue) as Total
  from cosd.ERSDataValues a ,cosd.ERSTimeDimension_LU 
 b,cosd.ERSGeographyDimension_LU c
where   a.ERSDataValues_ERSCommodity_ID  in (SELECT 
ERSBusinessLogic_InputDataSeries
FROM [AnimalProductsCoSD].[CoSD].[ERSBusinessLogic]
where ERSBusinessLogic_InputGeographyDimensionID = 7493
and ERSBusinessLogic_InputTimeDimensionValue = 'all months'
 and ERSBusinessLogic_Type = 'time aggregate')
 and a.ERSDataValues_ERSTimeDimension_ID = b.ERSTimeDimension_ID
and c.ersGeographyDimension_country != 'WORLD'
and a.ERSDataValues_ERSGeography_ID=c.ERSGeographyDimension_ID
group by b.ERSTimeDimension_Year,a.ERSDataValues_ERSCommodity_ID,
c.ersGeographyDimension_country
order by b.ERSTimeDimension_Year,a.ERSDataValues_ERSCommodity_ID

这是更新后的查询，我知道它很长，但我面临同样的问题，当我手动求和时总和与值不匹配

标签： sql-serverjoin

解决方案

这很可能是您的 JOIN 的问题（您在技术上并未使用）。尝试删除 SUM 和 GROUP 函数，看看您是否得到重复的行（如果这是一个大型数据集，可能会限制您的时间范围以获取更少的行）：

SELECT  b.TimeDimension_Year ,
        c.GeographyDimension_country ,
        a.DataValues_AttributeValue
FROM    cosd.DataValues a ,
        cosd.TimeDimension_LU b ,
        cosd.GeographyDimension_LU c
WHERE   a.DataValues_Commodity_ID = 2257
        AND a.DataValues_TimeDimension_ID = b.TimeDimension_ID
        AND c.GeographyDimension_ID = 7493
        AND b.TimeDimension_Year = 2015;

推荐阅读