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语句。