CONDITION ERA‎ > ‎

CE04: Conditions, stratified by year, age group and gender

This query is used to count conditions (condition_concept_id) across all condition era records stratified by year, age group and gender (gender_concept_id). The age groups are calculated as 10 year age bands from the age of a person at the condition era start date. The input to the query is a value (or a comma-separated list of values) of a condition_concept_id , year, age_group (10 year age band) and gender_concept_id. If the input is ommitted, all existing value combinations are summarized..

Input:
 Parameter  Example  Mandatory  Notes
condition_concept_id    
gender_concept_id    
gender_concept_id    
age_group    

Sample query run:
The following is a sample run of the query. The input parameters are highlighted in blue
SELECT condition, year, age_grp, gender, count(*)
  FROM -- person/condition/year/age/gender
     ( SELECT person.person_id
            , cond_name.concept_name AS condition
            , EXTRACT( YEAR FROM condition_era_start_date ) AS year
            , gender.concept_name As GENDER
            , EXTRACT( YEAR FROM condition_era_start_date ) - year_of_birth AS age
            , age_grp
         FROM condition_era condition
         JOIN vocabulary.concept cond_name ON cond_name.concept_id = condition_concept_id
         JOIN person ON person.person_id = condition.person_id
         JOIN vocabulary.concept gender ON gender.concept_id = gender_concept_id
         JOIN age_age_grp ON age = EXTRACT( YEAR FROM CONDITION_ERA_START_DATE ) - year_of_birth
     )
 GROUP BY condition, year, age_grp, gender
 ORDER BY condition, year, age_grp, gender;
Output:
Output field list:
 Field  Description
condition_era_start_date The start date for the condition era constructed from the individual instances of condition occurrences. It is the start date of the very first chronologically recorded instance of the condition.
year_of_birth The year of birth of the person. For data sources with date of birth, the year is extracted. For data sources where the year of birth is not available, the approximate year of birth is derived based on any age group categorization available.
relationship_id The type of relationship as defined in the relationship table.
concept_id_1 A foreign key to the concept in the concept table associated with the relationship. Relationships are directional, and this field represents the source concept designation.
concept_id_2 A foreign key to the concept in the concept table associated with the relationship. Relationships are directional, and this field represents the destination concept designation.
relationship_name The text that describes the relationship type.
condition_concept_id A foreign key that refers to a standard condition concept identifier in the vocabulary.
person_id A foreign key identifier to the person who is experiencing the condition. The demographic details of that person are stored in the person table.
gender_concept_id A foreign key that refers to a standard concept identifier in the vocabulary for the gender of the person.

Sample output record:
 Field  Description
condition_era_start_date  
year_of_birth  
relationship_id  
concept_id_1  
concept_id_2  
relationship_name  
condition_concept_id  
person_id  
gender_concept_id  
Comments