聚合函数中的总和不正确 [英] Incorrect sum in an aggregate function

问题描述

我有一个问题是SQL给出了错误的查询总和，

I have the an issue with SQL giving wrong sums with the query,

select GL, sum(TotalFuel) as "tf" from fueltable1
where [TransDate] > '3/1/2015 12:00:00 AM'
and [TransDate] < '4/1/2015 12:00:00 AM'
group by GL

如果我将表加上燃料表1，我得到10405.63 ，但这导致总共9934.36。

任何想法？

推荐答案

最有可能的原因是条件是从聚合中消除行。例如，其中一个TransDate条件包括相等，例如

The likeliest reason is that the conditions are eliminating rows from aggregation. For example should either one of the TransDate conditions include equality, for example

where [TransDate] >= '3/1/2015 12:00:00 AM'
and [TransDate] < '4/1/2015 12:00:00 AM'

另一件事是你依赖隐式日期转换。在最坏的情况下，日期和月份会切换位置，所以我建议使用 CONVERT（日期，'3/1/2015'，101）（或者您使用的格式正确）



如果这些都没有说明原因，那么检查各行就会到位。不要对数据进行分组，而是选择源数据，将其保存到Excel并检查是否存在差异。

Another thing is that you're relying on the implicit date conversion. In worst case dates and months switch places so I'd suggest using CONVERT(date, '3/1/2015', 101) (or whatever is the correct format for you)

If neither of these reveal the reason, then checking the individual rows would be in place. Instead of grouping the data, select the source data, save it to excel and check if there is a difference.

这篇关于聚合函数中的总和不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！