Sql Avg() Returning The Wrong Result For 3 Columns

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 w

Solution 1:

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.

Solution 2:

The only possibility is presence of the NULL's in the rows in your table selects... if there is a null column the AVG will ignore it, instead of counting it...

DECLARE@STARTDATE DATETIME
DECLARE@ENDATE DATETIME

SET@STARTDATE='2013-05-01'SET@ENDATE='2013-05-31'SELECTDISTINCT 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(ISNULL(vr.len_of_stay,0)) AS'LOS'
, AVG(ISNULL(vr.drg_std_days_stay,0)) AS'DRG LOS BENCH'
, AVG(ISNULL((vr.len_of_stay - vr.drg_std_days_stay),0)) AS'LOS - DRG BENCH'FROM smsmir.vst_rpt vr
LEFTOUTERJOIN smsmir.pyr_plan pp
ON vr.pt_id = pp.pt_id
JOIN smsdss.pract_dim_v pv
ON vr.adm_pract_no = pv.src_pract_no

WHERE vr.adm_dtime BETWEEN@STARTDATEAND@ENDATEAND vr.vst_type_cd ='I'AND pv.spclty_desc !='NO DESCRIPTION'--AND pv.spclty_desc NOT LIKE 'HOSPITALIST%'AND vr.drg_std_days_stay ISNOTNULLAND pv.pract_rpt_name !='?'AND pv.orgz_cd ='s0x0'AND pv.med_staff_dept IN (
'INTERNAL MEDICINE',
'FAMILY PRACTICE',
'SURGERY'
)
GROUPBY pv.pract_rpt_name, pv.med_staff_dept
ORDERBY pv.med_staff_dept, AVG(vr.len_of_stay - vr.drg_std_days_stay)DESC