SQL AVG() 为 3 列返回错误的结果 [英] SQL AVG() Returning the wrong result for 3 columns
问题描述
我正在编写一个查询,它应该给我一个 count()
和三个 avg()
.count()
工作得很好,但 avg()
函数返回错误的结果.我正在处理的数据如下所示:
I am writing a query that is supposed to give me a count()
and three avg()
's. The count()
works just fine, but the avg()
functions are returning erroneous results. The data I am working with looks like this:
MD Name | PT | Med Staff | LOS | DRG Bench | LOS - Bench
MCP | 12345 | Ortho SX | 5 | 4 | 1
MCP | 25879 | Ortho SX | 3 | 5 | -2
MCP | 98556 | Ortho SX | 4 | 5 | -1
...
我想要的输出是:
MD Name | # PT | Med Staff | Avg LOS | Avg DRG Bench | AVG LOS - Bench
MCP | 3 | Ortho SX | 4 | 4.66 | 0
我得到的平均值结果不正确.我有一种情况,特别是我有以下情况:
I am getting incorrect results for the averages. I have one case in particular where I have the following:
MD Name | LOS | Bench | LOS - Bench
MCP | 2.0000 | 1.8000 | 0.2000
MCP | 1.0000 | 1.7000 | -0.7000
MCP | 25.0000| 4.9000 | 20.1000
MCP | 4.0000 | 2.2000 | 1.8000
对于和 AVG LOS,我得到 9.000000 而不是 8.000000,对于 AVG Bench,我得到 2.780000 而不是 2.65,对于 LOS-Bench,我得到 6.220000 而不是 5.35,这些是显着差异,我必须是正确的两位小数.
For and AVG LOS I am getting 9.000000 instead of 8.000000, for the AVG Bench I am getting 2.780000 instead of 2.65 and for the LOS-Bench I am getting 6.220000 instead of 5.35, these are significant differences and I must be correct to two decimal places.
这是我使用的 SQL,SQL Server 2008
Here is the SQL I am using, SQL Server 2008
DECLARE @STARTDATE DATETIME
DECLARE @ENDATE DATETIME
SET @STARTDATE = '2013-05-01'
SET @ENDATE = '2013-05-31'
SELECT DISTINCT pv.pract_rpt_name AS 'PHYSICIAN'
, COUNT(DISTINCT vr.pt_id) AS '# PTS'
--, pv.spclty_desc AS 'SPECIALTY'
, pv.med_staff_dept AS 'MED STAFF'
, AVG(vr.len_of_stay) AS 'LOS'
, AVG(vr.drg_std_days_stay) AS 'DRG LOS BENCH'
, AVG(vr.len_of_stay - vr.drg_std_days_stay) AS 'LOS - DRG BENCH'
FROM smsmir.vst_rpt vr
LEFT OUTER JOIN smsmir.pyr_plan pp <-- removed and fixed
ON vr.pt_id = pp.pt_id <-- removed and fixed
JOIN smsdss.pract_dim_v pv
ON vr.adm_pract_no = pv.src_pract_no
WHERE vr.adm_dtime BETWEEN @STARTDATE AND @ENDATE
AND vr.vst_type_cd = 'I'
AND pv.spclty_desc != 'NO DESCRIPTION'
--AND pv.spclty_desc NOT LIKE 'HOSPITALIST%'
AND vr.drg_std_days_stay IS NOT NULL
AND pv.pract_rpt_name != '?'
AND pv.orgz_cd = 's0x0'
AND pv.med_staff_dept IN (
'INTERNAL MEDICINE',
'FAMILY PRACTICE',
'SURGERY'
)
GROUP BY pv.pract_rpt_name, pv.med_staff_dept
ORDER BY pv.med_staff_dept, AVG(vr.len_of_stay - vr.drg_std_days_stay)DESC
感谢您的时间和努力.
推荐答案
您在查询中的 OUTER JOIN 可能会影响 AVG 函数操作的行数.如果您不需要它(而且我在您的查询中的其他地方都看不到该表被引用的任何地方),请尝试将其删除.
The OUTER JOIN you have in the query may be affecting the number of rows that the AVG function is operating over. If you don't need it (and I can't see anywhere where that table is referenced elsewhere in your query) try removing it.
这篇关于SQL AVG() 为 3 列返回错误的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!