Extract Rows with Values >100 in a Variable Showing All Columns on MySQL Workbench

Question

I have a large data set which has data like:

Cancer_Type    Age     Crude_Rate
Melanoma       20-24   30.5
Lung           70-74   212
Myeloma        55-59   101.8
Leukemia       35-39   77.9

I want to extract all columns with the rows that have Crude_Rates greater than 100 i.e. for the data snippet above what would be extracted is:

Cancer_Type    Age     Crude_Rate
Lung           70-74   212
Myeloma        55-59   101.8

I am trying the following code on MySQL Workbench:

SELECT Cancer_Type, Gender, Crude_Rate
FROM cancer.agescancer
WHERE Crude_Rate=(SELECT Crude_Rate WHERE Crude_Rate>100 FROM cancer.agescancer) 

I am getting the following error:

 Error Code: 1064. You have an error in your SQL syntax; check the manual   that corresponds to your MySQL server version for the right syntax to use near 'FROM cancer.agescancer)' at line 3

And the 100 in my code is underlined in red. I am not sure what I have done wrong.

Thank you in advanced for help

Answer 1

Simply change your query with,

SELECT Cancer_Type, Gender, Crude_Rate
FROM cancer.agescancer
WHERE Crude_Rate >100