Friends:
Looks like I need to back out a bit here of a debate that wasn’t the intention. Or better yet, jump into that one as well. There is nothing better than a 2-front war. 
Debate #1: Characterize the conditions of a population. That’s what @pandamiao started this debate with. Skip this if you are interested in the Table 1 discussion.
This solves the following problem: If you have a bunch of patients and you want to summarize what diseases they have. Since there are many of them, you need categories. The above script puts each Condition concept into one of 20 clinically meaningful statistical categories. The categories are:
- Blood disease
- Injury and poisoning
- Congenital disease
- Pregnancy or childbirth disease
- Perinatal disease
- Infection
- Neoplasm
- Endocrine or metabolic disease
- Mental disease
- Nerve disease and pain
- Eye disease
- ENT disease
- Cardiovascular disease
- Respiratory disease
- Digestive disease
- Skin disease
- Soft tissue or bone disease
- Genitourinary disease
- Iatrogenic condition
- Not categorized
These categories are very similar, but not exactly the same as the 22 ICD-10 Chapters. The differences are threefold:
- The names are much shorter (for easy reporting)
- Some of the Chapters are really not Conditions in our world: Symptoms, signs and abnormal clinical and laboratory findings, not elsewhere classified, External causes of morbidity and mortality and Factors influencing health status and contact with health services. Instead, we have “Iatrogenic condition”.
- All standard (not ICD-10) concepts are rolled up using SNOMED, which will position some of them into a different category than ICD-10 would have put it.
If you want to use the script, join the concept_id of the main select to the condition_concept_id of your CONDITION_OCCURRENCE table and count up the different category_name occurrences. The total will be equal to the total rows in the table, so the % add up to 100.
The next step is to build a similar script, but with about 100 finer grained categories. Otherwise same idea.