CONDITION ERA‎ > ‎

CE17: Distribution of condition occurrence count, stratified by condition and condition type

This query is used to provide summary statistics for condition occurrence count (condition_occurrence_count) across all condition era records stratified by condition (condition_concept_id) and condition type (condition_type_concept_id, in CDM V2 condition_occurrence_type): the mean, the standard deviation, the minimum, the 25th percentile, the median, the 75th percentile, the maximum and the number of missing values. The input to the query is a value (or a comma-separated list of values) of a condition_concept_id and a condition_type_concept_id. If the input is omitted, all existing value combinations are summarized.

Input:
 Parameter  Example  Mandatory  Notes
condition_concept_id 254761, 257011, 320128, 432867, 25297 No  
condition_type_concept_id   No  

Sample query run:
The following is a sample run of the query. The input parameters are highlighted in blue
SELECT condition_concept_id, condition_type_concept_id
     , min( occurrences ) AS min
     , max( occurrences ) AS max
     , round( avg( occurrences ), 2 ) AS avg
     , round( stdDev( occurrences ), 1 ) AS stdDev
     , PERCENTILE_DISC(0.25) WITHIN GROUP( ORDER BY occurrences ) 
                                     AS percentile_25
     , PERCENTILE_DISC(0.5)  WITHIN GROUP (ORDER BY occurrences )
                                     AS median
     , PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY occurrences )
                                     AS percential_75
  FROM /* condition_concept, condition_type, occurrences */
     ( SELECT person_id, condition_concept_id, condition_type_concept_id
            , count(*) AS occurrences
         FROM condition_era 
       WHERE condition_concept_id IN( 254761, 257011, 320128, 432867, 25297 )
       GROUP by person_id, condition_concept_id, condition_type_concept_id
     )
 GROUP BY condition_concept_id, condition_type_concept_id;
Output:
Output field list:
 Field  Description
condition_type_concept_id A foreign key to the predefined concept identifier in the vocabulary reflecting the parameters used to construct the condition era.
condition_concept_id A foreign key that refers to a standard condition concept identifier in the vocabulary.

Sample output record:
 Field  Description
condition_type_concept_id  
condition_concept_id  
Comments