Count Occurrences in String

mc1392

I have a string where I am trying to count occurrences of a string within a pipe separated list of items, a string.

 

ARDS|Lung Cancers|Pleural Effusion|Pleural Infections|Bronchoscopy|Interventional Pulmonary|Pneumothorax|Non-Invasive Ventilation|Transplantation|Shock|Mechanical Ventilation|Pleura and Chest Wall Diseases Other|Sepsis|Respiratory Failure/Injury|Pulmonary Embolism/Deep Vein Thrombosis|Systemic Diseases Affecting the Respiratory System|Nontuberculous Mycobacteria Infection|Mesothelioma

 

When I try

• select where `Interest Areas` IN('Pleural Effusion')
• select where `Interest Areas` IS NULL
• select where `Interest Areas` = ' '
• select where LOCATE(`Interest Areas`, 'Pleural Effusion')
• select where `Interest Areas` CONTAINS('Pleural Effusion')

it fails!

 

I've tried so many ways but I am not getting the proper count for each grouping. The total count works, but not the individual counts.

Here is my query:

 

SELECT SessionNumber,EventDate,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Pleural Effusion') then 1 end ) As Pleural_Effusion_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Pleural Effusion') then 1 end ) As Pleural_Effusion_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Non-Invasive Ventilation') then 1 end ) As Non_Invasive_Ventilation_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Non-Invasive Ventilation') then 1 end ) As Non_Invasive_Ventilation_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Pleura and Chest Wall Diseases Other') then 1 end ) As Pleura_and_Chest_Wall_Diseases_Other_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Pleura and Chest Wall Diseases Other') then 1 end ) As Pleura_and_Chest_Wall_Diseases_Other_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Mesothelioma') then 1 end ) As Mesothelioma_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Mesothelioma') then 1 end ) As Mesothelioma_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Pulmonary Embolism/Deep Vein Thrombosis') then 1 end ) As Pulmonary_Embolism_Deep_Vein_Thrombosis_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Pulmonary Embolism/Deep Vein Thrombosis') then 1 end ) As Pulmonary_Embolism_Deep_Vein_Thrombosis_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Duchenne Muscular Dystrophy') then 1 end ) As Duchenne_Muscular_Dystrophy_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Duchenne Muscular Dystrophy') then 1 end ) As Duchenne_Muscular_Dystrophy_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'ARDS') then 1 end ) As ARDS_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'ARDS') then 1 end ) As ARDS_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Myasthenia Gravis') then 1 end ) As Myasthenia_Gravis_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Myasthenia Gravis') then 1 end ) As Myasthenia_Gravis_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Parasomnias') then 1 end ) As Parasomnias_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Parasomnias') then 1 end ) As Parasomnias_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Bronchoscopy') then 1 end ) As Bronchoscopy_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Bronchoscopy') then 1 end ) As Bronchoscopy_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Pleural Infections') then 1 end ) As Pleural_Infections_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Pleural Infections') then 1 end ) As Pleural_Infections_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Pneumothorax') then 1 end ) As Pneumothorax_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Pneumothorax') then 1 end ) As Pneumothorax_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Alpha-1 Antitrypsin') then 1 end ) As Alpha_1_Antitrypsin_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Alpha-1 Antitrypsin') then 1 end ) As Alpha_1_Antitrypsin_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Global Health') then 1 end ) As Global_Health_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Global Health') then 1 end ) As Global_Health_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Asthma') then 1 end ) As Asthma_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Asthma') then 1 end ) As Asthma_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Lymphangioleiomyomatosis') then 1 end ) As Lymphangioleiomyomatosis_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Lymphangioleiomyomatosis') then 1 end ) As Lymphangioleiomyomatosis_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Area') then 1 end ) As Area_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Area') then 1 end ) As Area_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'CAP') then 1 end ) As CAP_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'CAP') then 1 end ) As CAP_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Chronic Thromboembolic Pulmonary Hypertension') then 1 end ) As Chronic_Thromboembolic_Pulmonary_Hypertension_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Chronic Thromboembolic Pulmonary Hypertension') then 1 end ) As Chronic_Thromboembolic_Pulmonary_Hypertension_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Tuberculosis') then 1 end ) As Tuberculosis_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Tuberculosis') then 1 end ) As Tuberculosis_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Mechanical Ventilation') then 1 end ) As Mechanical_Ventilation_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Mechanical Ventilation') then 1 end ) As Mechanical_Ventilation_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Sepsis') then 1 end ) As Sepsis_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Sepsis') then 1 end ) As Sepsis_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Respiratory Failure/Injury') then 1 end ) As Respiratory_Failure_Injury_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Respiratory Failure/Injury') then 1 end ) As Respiratory_Failure_Injury_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Infectious Lung Diseases Other') then 1 end ) As Infectious_Lung_Diseases_Other_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Infectious Lung Diseases Other') then 1 end ) As Infectious_Lung_Diseases_Other_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Non-specific Interstitial Pn') then 1 end ) As Non_specific_Interstitial_Pn_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Non-specific Interstitial Pn') then 1 end ) As Non_specific_Interstitial_Pn_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Shock') then 1 end ) As Shock_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Shock') then 1 end ) As Shock_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Pneumonia') then 1 end ) As Pneumonia_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Pneumonia') then 1 end ) As Pneumonia_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Sleep-Related and Neuromuscular Breathing Disorders Other') then 1 end ) As Sleep_Neuromuscular_Breathing_Disorders_Other_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Sleep-Related and Neuromuscular Breathing Disorders Other') then 1 end ) As Sleep_Neuromuscular_Breathing_Disorders_Other_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Pulmonary Vascular Diseases Other') then 1 end ) As Pulmonary_Vascular_Diseases_Other_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Pulmonary Vascular Diseases Other') then 1 end ) As Pulmonary_Vascular_Diseases_Other_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'HAP/VAP') then 1 end ) As HAP_VAP_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'HAP/VAP') then 1 end ) As HAP_VAP_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Pulmonary Rehabilitation') then 1 end ) As Pulmonary_Rehabilitation_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Pulmonary Rehabilitation') then 1 end ) As Pulmonary_Rehabilitation_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Interstitial Lung Diseases Other') then 1 end ) As Interstitial_Lung_Diseases_Other_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Interstitial Lung Diseases Other') then 1 end ) As Interstitial_Lung_Diseases_Other_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Transplantation') then 1 end ) As Transplantation_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Transplantation') then 1 end ) As Transplantation_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Primary Ciliary Dyskinesia') then 1 end ) As Primary_Ciliary_Dyskinesia_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Primary Ciliary Dyskinesia') then 1 end ) As Primary_Ciliary_Dyskinesia_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Environmental Exposure-Related and Occupational Lung Diseases') then 1 end ) As Env_Exposure_Related_Occupational_Lung_Diseases_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Environmental Exposure-Related and Occupational Lung Diseases') then 1 end ) As Env_Exposure_Related_Occupational_Lung_Diseases_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Lung Cancers') then 1 end ) As Lung_Cancers_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Lung Cancers') then 1 end ) As Lung_Cancers_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Non-specific Interstitial Pneumonitis') then 1 end ) As Non_specific_Interstitial_Pneumonitis_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Non-specific Interstitial Pneumonitis') then 1 end ) As Non_specific_Interstitial_Pneumonitis_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Interventional Pulmonary') then 1 end ) As Interventional_Pulmonary_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Interventional Pulmonary') then 1 end ) As Interventional_Pulmonary_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Sickle Cell Disease') then 1 end ) As Sickle_Cell_Disease_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Sickle Cell Disease') then 1 end ) As Sickle_Cell_Disease_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Narcolepsy') then 1 end ) As Narcolepsy_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Narcolepsy') then 1 end ) As Narcolepsy_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Pulmonary Hypertension') then 1 end ) As Pulmonary_Hypertension_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Pulmonary Hypertension') then 1 end ) As Pulmonary_Hypertension_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Scleroderma') then 1 end ) As Scleroderma_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Scleroderma') then 1 end ) As Scleroderma_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'HIV/AIDS') then 1 end ) As HIV_AIDS_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'HIV/AIDS') then 1 end ) As HIV_AIDS_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Restless Leg Syndrome') then 1 end ) As Restless_Leg_Syndrome_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Restless Leg Syndrome') then 1 end ) As Restless_Leg_Syndrome_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Cystic Fibrosis') then 1 end ) As Cystic_Fibrosis_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Cystic Fibrosis') then 1 end ) As Cystic_Fibrosis_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Sarcoidosis') then 1 end ) As Sarcoidosis_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Sarcoidosis') then 1 end ) As Sarcoidosis_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'No Response') then 1 end ) As No_Response_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'No Response') then 1 end ) As No_Response_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Nontuberculous Mycobacteria Infection') then 1 end ) As Nontuberculous_Mycobacteria_Infection_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Nontuberculous Mycobacteria Infection') then 1 end ) As Nontuberculous_Mycobacteria_Infection_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Idiopathic Pulmonary Fibrosis') then 1 end ) As Idiopathic_Pulmonary_Fibrosis_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Idiopathic Pulmonary Fibrosis') then 1 end ) As Idiopathic_Pulmonary_Fibrosis_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Bronchiectasis') then 1 end ) As Bronchiectasis_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Bronchiectasis') then 1 end ) As Bronchiectasis_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Fungal Lung Diseases') then 1 end ) As Fungal_Lung_Diseases_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Fungal Lung Diseases') then 1 end ) As Fungal_Lung_Diseases_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Cough') then 1 end ) As Cough_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Cough') then 1 end ) As Cough_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Air Movement and Airways Diseases Other') then 1 end ) As Air_Movement_and_Airways_Diseases_Other_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Air Movement and Airways Diseases Other') then 1 end ) As Air_Movement_and_Airways_Diseases_Other_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Systemic Diseases Affecting the Respiratory System') then 1 end ) As Systemic_Diseases_Affecting_the_Respiratory_System_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Systemic Diseases Affecting the Respiratory System') then 1 end ) As Systemic_Diseases_Affecting_the_Respiratory_System_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Rare Lung Diseases') then 1 end ) As Rare_Lung_Diseases_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Rare Lung Diseases') then 1 end ) As Rare_Lung_Diseases_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Obstructive Sleep Apnea') then 1 end ) As Obstructive_Sleep_Apnea_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Obstructive Sleep Apnea') then 1 end ) As Obstructive_Sleep_Apnea_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'Congenital Genetic and Developmental Lung Diseases Other') then 1 end ) As Congntl_Genetic_Developmental_Lung_Diseases_Other_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'Congenital Genetic and Developmental Lung Diseases Other') then 1 end ) As Congntl_Genetic_Developmental_Lung_Diseases_Other_INTERNATIONAL,
 count( case when Country = "United States" and LOCATE(`Interest Areas`, 'COPD') then 1 end ) As COPD_DOMESTIC,
 count( case when Country <> "United States" and LOCATE(`Interest Areas`, 'COPD') then 1 end ) As COPD_INTERNATIONAL,
count(*) As session_total_count FROM private_merge GROUP BY SessionNumber,EventDate order by EventDate, SessionNumber, session_total_count;