How to check if a string exists in a list of strings in case statement in mysql?-CodePudding

I am pretty new to Mysql query and I am trying to get my head around a basic query below.

I have a table users and it has a field country.

Below is the query that i wrote to compare the country with a list of countries to create a column region using case statement. It is throwing a syntax error. What is a correct way to write the query without using multiple OR in the case statement ?

SELECT
     usr.id
    , usr.country
    , CASE 
        WHEN usr.country IN ('USA','CANADA','BRAZIL') THEN 'AMERICA'
        WHEN usr.country IN ('INDIA','SRI LANKA','NEPAL','BHUTAN') THEN 'ASIA'
      END AS region
FROM
   users usr
GROUP BY
   usr.id, usr.country
ORDER BY
   usr.id

-Thanks, Vinit

CodePudding user response：

Your CASE expression (not statement) is fine, but your GROUP BY logic has an issue. It is not allowed, in certain MySQL server modes, to group by two columns and then select by any other column. Try using this version:

SELECT id,
       CASE WHEN country IN ('USA', 'CANADA', 'BRAZIL') THEN 'AMERICA'
            WHEN country IN ('INDIA', 'SRI LANKA', 'NEPAL', 'BHUTAN') THEN 'ASIA'
       END AS region
FROM users
GROUP BY 1, 2
ORDER BY 1;

Here we are grouping by just the user id and mapped country, and then selecting the same. Actually, since you are not selecting any aggregates it might not even make sense to use GROUP BY here. But if you did want to do that, the above syntax should work without error.