Forum Discussion
Distinct Count Subquery, MS Access
Need some help in getting a Distinct Count result in this query. For context, i work in healthcare and of course an not share the actual data behind the query. My goal is to modify the current quie...
I would try this:
SELECT q1.POST_DATE
, q1.TX_FIN_CLASS
, q1.ACCT_CLASS
, q1.PRIMARY_PLAN_NAME
, Sum(q1.SumPerHoAcc) AS SumOfORIG_AMT
, Sum(q1.NumberOfDistinctHospitalAccounts) AS CountOfDistinctHospitalAccounts
FROM (
SELECT ez_hosp_chg_v.POST_DATE
, ez_hosp_chg_v.TX_FIN_CLASS
, ez_hosp_chg_v.ACCT_CLASS
, ez_hosp_chg_v.PRIMARY_PLAN_NAME
, ez_hosp_chg_v.HOSP_ACCT_ID
, Sum(ez_hosp_chg_v.ORIG_AMT) AS SumPerHoAcc
, 1 AS NumberOfDistinctHospitalAccounts
FROM ez_hosp_chg_v
WHERE ez_hosp_chg_v.POST_DATE BETWEEN [Enter start date]
AND [Enter end date]
GROUP BY ez_hosp_chg_v.POST_DATE
, ez_hosp_chg_v.TX_FIN_CLASS
, ez_hosp_chg_v.ACCT_CLASS
, ez_hosp_chg_v.PRIMARY_PLAN_NAME
, ez_hosp_chg_v.HOSP_ACCT_ID
) AS q1
GROUP BY q1.POST_DATE
, q1.TX_FIN_CLASS
, q1.ACCT_CLASS
, q1.PRIMARY_PLAN_NAME;