CO03: What is specialty that diagnosed a condition.

Returns the distribution of the physician's specialty who diagnosed a certain condition.

Input:
 Parameter  Example  Mandatory  Notes
condition_concept_id 31967 Yes Condition concept ID for 'Nausea'

Sample query run:
The following is a sample run of the query. The input parameters are highlighted in blue
SELECT concept_name AS Specialty, 
       specialty_freq
FROM (SELECT specialty_concept_id, count(*) AS specialty_freq
      FROM (SELECT *
	    FROM (select associated_provider_id from condition_occurrence 
	    WHERE condition_concept_id = 31967
	      AND associated_provider_id IS NOT NULL) AS from_cond
            LEFT JOIN (SELECT provider_id, specialty_concept_id 
	    FROM provider) AS from_prov
       ON from_cond.associated_provider_id=from_prov.provider_id
    )
    GROUP BY specialty_concept_id
    ORDER BY specialty_freq
    ) AS spec_id_count
LEFT JOIN (SELECT concept_id, concept_name 
           FROM vocabulary.concept) AS spec_concept
ON spec_id_count.specialty_concept_id=spec_concept.concept_id
ORDER BY specialty_freq DESC
Output:
Output field list:
 Field  Description
Specialty Physician's specialty.
Specialty_freq Frequency of the specialty recording medical condition

Sample output record:
 Field  Description
Specialty Internal Medicine
Specialty_freq 10324
Comments