CO07: Frequency of hospitalized for a condition

Returns the distribution of number of times a person has been hospitalized where a certain condition was reported.

 Parameter  Example  Mandatory  Notes
condition_concept_id 31967 Yes Condition concept identifier for 'Nausea'

Sample query run:
The following is a sample run of the query. The input parameters are highlighted in blue
SELECT number_of_hospitlizations, 
       count(*) AS persons_freq
  SELECT person_id, COUNT(*) AS number_of_hospitlizations
  FROM (
      SELECT distinct condition_era_id, era.person_id  
      FROM (
          SELECT condition_start_date, condition_end_date, person_id
          FROM (SELECT visit_occurrence_id, condition_start_date, condition_end_date, person_id 
                FROM   condition_occurrence 
                WHERE  condition_concept_id=31967
                AND    visit_occurrence_id IS NOT NULL) AS FROM_cond
          JOIN (SELECT visit_occurrence_id, place_of_service_concept_id 
                FROM   visit_occurrence 
                WHERE  place_of_service_concept_id=9201) AS FROM_visit
          ON FROM_cond.visit_occurrence_id=FROM_visit.visit_occurrence_id
         ) AS occurr,
          SELECT condition_era_id, person_id, condition_era_start_date, condition_era_end_date
          FROM   condition_era
          WHERE  condition_concept_id=31967
         ) AS era
      WHERE era.person_id=occurr.person_id 
        AND era.condition_era_start_date <= occurr.condition_end_date 
        AND (era.condition_era_end_date IS NULL OR era.condition_era_end_date >= occurr.condition_start_date)
  GROUP BY person_id
  ORDER BY number_of_hospitlizations desc
GROUP BY number_of_hospitlizations
ORDER BY persons_freq desc
Output field list:
 Field  Description
number_of_hospitlizations Number of times a person was reported to be hospitalized with a certain condition.
persons_freq Number of persons which were reported to have a certain number of hospilizations with a certain condition.

Sample output record:
 Field  Description
number_of_hospitlizations 2
persons_freq 177