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 |
|
|
|