SQL Server聚合提供异常值

时间:2020-12-21 22:42:15

I have these two queries to test my output

我有两个查询来测试我的输出

SELECT DISTINCT( providerId ), 
               SUM(( claimCount )), 
               SUM(paidAmount), 
               SUM(provallowed), 
               SUM(patresp), 
               provId, 
               cliCode, 
               asctype, 
               payerid, 
               procCode 
FROM   TempCounts tt, 
       TempClient tct 
WHERE  ( tct.npi = tt.provid 
          OR tct.txcode = tt.provid ) 
       AND CLICODE = 'XXX' 
GROUP  BY tt.provid, 
          providerId, 
          cliCode, 
          asctype, 
          payerid, 
          procCode 

--

SELECT DISTINCT( providerid ), 
               claimCount, 
               paidamount, 
               provallowed, 
               patresp, 
               provid, 
               CLICODE, 
               asctype, 
               payerid, 
               proccode 
FROM   TempCounts tt, 
       TempClient tct 
WHERE  ( tct.npi = tt.provid 
          OR tct.txcode = tt.provid ) 
       AND CLICODE = 'XXX' 

summing the unaggregated results from the second query should give the aggregated values in the first query, but my data sets end up like first data set

对第二个查询的未聚合结果求和应该在第一个查询中给出聚合值,但我的数据集最终像第一个数据集一样

15086   7   216.16  0.00    35.00   1609950203  XXX     216     72040
15086   7   227.36  0.00    21.00   1609950203  XXX     216     72070

second data set

第二个数据集

15086   1   30.88   0.00    5.00    1609950203  XXX     216     72040
15086   1   32.48   0.00    3.00    1609950203  XXX     216     72070

Can't for the life of me see what I'm missing and stared at it so long I'm code blind. Any suggestions?

不能为我的生活看到我所缺少的东西,并且盯着它这么久我是代码失明的。有什么建议么?

2 个解决方案

#1


3  

The two data sets can be different because of the DISTINCT option.

由于DISTINCT选项,这两个数据集可能不同。

Therefore, the unaggregated query returns only a limited number of lines, those which are different from each other.

因此,非聚合查询仅返回有限数量的行,这些行彼此不同。

On the first query, the DISTINCT option is useless since you have all grouping columns in the result.

在第一个查询中,DISTINCT选项没用,因为结果中包含所有分组列。

#2


0  

I figured it out finally. because the tempClient table has multiple records based on the npi and txcode field (i.e. a record can have multiple instances of txcode for each npi. I thought I was filtering for that as the same query against mysql gave correct results. The solution was adding tt.npi and tt.txcode to the groupby statement.

我终于明白了。因为tempClient表有多个基于npi和txcode字段的记录(即每个npi的记录可以有多个txcode实例。我以为我正在过滤,因为针对mysql的相同查询给出了正确的结果。解决方案是添加tt .npi和tt.txcode到groupby语句。

#1


3  

The two data sets can be different because of the DISTINCT option.

由于DISTINCT选项,这两个数据集可能不同。

Therefore, the unaggregated query returns only a limited number of lines, those which are different from each other.

因此,非聚合查询仅返回有限数量的行,这些行彼此不同。

On the first query, the DISTINCT option is useless since you have all grouping columns in the result.

在第一个查询中,DISTINCT选项没用,因为结果中包含所有分组列。

#2


0  

I figured it out finally. because the tempClient table has multiple records based on the npi and txcode field (i.e. a record can have multiple instances of txcode for each npi. I thought I was filtering for that as the same query against mysql gave correct results. The solution was adding tt.npi and tt.txcode to the groupby statement.

我终于明白了。因为tempClient表有多个基于npi和txcode字段的记录(即每个npi的记录可以有多个txcode实例。我以为我正在过滤,因为针对mysql的相同查询给出了正确的结果。解决方案是添加tt .npi和tt.txcode到groupby语句。