Payer mix is the share of a health system's business from each kind of payer: Medicare, Medicaid, commercial plans and self-pay. Volume mix counts encounters; dollar mix sums billed charges. The two differ, because inpatient stays bill far more than clinic visits and their payers differ.
SELECT p.payer_type,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM hd_encounters), 1) AS encounter_pct
FROM hd_encounters AS e
JOIN hd_payers AS p ON p.payer_id = e.payer_id
GROUP BY p.payer_type;
Check the grain before you count:
- One row of
hd_encounters is an encounter. hd_claims has no row yet for a stay still open, and hd_patients counts people.
- Join claims to encounters on
encounter_id. Joining on payer_id pairs every claim with every encounter of the same payer.
COUNT(*) * 100 / total is integer division: use 100.0.
SUM(COUNT(*)) OVER () totals the groups after WHERE, which suits a share within a filtered set.
Payer mix by volume.