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

Questions

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

December 21, 2021

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.

>Solution :

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