SELECT Allergen1.Code, Allergen1.TypeCode, Allergen1.Description, Allergen1.ExternalDrugCode, Counts.AllergyCount
FROM CV3Allergen Allergen1
INNER JOIN
        (SELECT Allergen.GUID, Count(NULLIF(Allergy.AllergenGUID,0)) AllergyCount
        FROM CV3Allergen Allergen
        LEFT JOIN CV3AllergyDeclaration Allergy
        ON Allergy.AllergenGUID = Allergen.GUID
  AND Allergy.Status = 'Active'
  AND Allergen.TypeCode IN ('Drug', 'Drug Category')
        GROUP BY Allergen.GUID) Counts
ON Allergen1.GUID = Counts.GUID
ORDER BY 5 DESC, 1

Return counts for each drug / drug category allergen

Allergy / Allergen Counts

SQL