How to use Conditional WHERE Clause in MySql under this kind of circumstance?-CodePudding

I'm new to data analytics and I have just met a problem while doing a practice, my practice data is as blow:practice_movie_data

So the practice requires sorting the data via Release Data while conditioning the Genre as Comedy and Revenue to be greater than $300 million. The tutorial showed the case on Bigquery will run successfully as like this:

SELECT
    * 
FROM
    movie_data
WHERE
    Genre = 'Comedy'
    AND Revenue > 300000000
ORDER BY 
    `Release Date`
    DESC

But when I tried it on Mysql, there are no results shown as I can tell the data type for the Revenue Column is Varchar(255), so I tried this:

SELECT
    * 
FROM
    movie_data
WHERE
    Genre = 'Comedy'
    AND CAST(Revenue AS FLOAT) AND Revenue > 300000000)
ORDER BY 
    `Release Date`
    DESC

Unfortunately, I got errors, please help me with this! I appreciate all the helpful answers.

CodePudding user response：

Try removing the unwanted characters then converting / casting it:

SELECT
    * 
FROM
    movie_data
WHERE
    Genre = 'Comedy'
    AND CONVERT(REPLACE(REPLACE(Revenue,'$',''),',',''),DECIMAL(12,2)) > 300000000
ORDER BY 
    `Release Date`
    DESC