hd_diagnoses has one row per code per encounter. seq 1 (also flagged is_principal) is the principal diagnosis, the condition chiefly responsible for the stay; the rest are secondary. Join it to encounters on encounter_id.
ICD-10-CM codes are built for prefix searches. The first three characters are the category: I50 heart failure, E11 type 2 diabetes. LIKE 'I50%' finds every code in the category.
SELECT COUNT(DISTINCT e.patient_id) AS patients
FROM hd_diagnoses d
JOIN hd_encounters e ON e.encounter_id = d.encounter_id
WHERE d.icd10_code LIKE 'E11%';
Three traps:
- Rows are not patients. A patient with four visits has four diagnosis rows. Count
DISTINCT patient_id when the question is about people.
- Position matters. Admitted for pneumonia means pneumonia is the principal diagnosis (
seq = 1). A secondary code says the condition was present, not that it was the reason.
- Prefix width. Use the whole three-character category.
'I%' takes every circulatory code, and 'I5%' would also take other heart categories in the full code set that are not heart failure.
Patients with any type 2 diabetes code, each counted once.