CODEWITHSUNDEEP
mysqlsqlstringcase
Vivi
Vivi

Reputation: 11

use case when to show column conditional value in MySQL

I would like to add a column and show customer rewards status based on their points. However, MySQL states there is an error in the syntax which I cannot figure out why. My code is as below, I got the error msg that SELECT is not valid at this position.

SELECT customer_id, first_name,
CASE points
    WHEN > 3000 THEN 'Gold'
    WHEN BETWEEN 2000 to 3000 THEN 'Silver'
    ELSE 'Bronze'
END AS rewards_status
FROM customers

Upvotes: 1

Views: 34

Answers (2)

GMB
GMB

Reputation: 222492

The short syntax for case, which you are using here (like: case <expr> when <val> then ... end) only supports equality condition; this does not fit your use case, which requires inequality conditions. You need to use the long case syntax (like: case when <condition> then ... end).

Also, there is no need for between in the second condition. Branches of a case expression are evaluated sequentially, and the process stops as soon as a condition is fullfilled, so you can do:

select
    customer_id,
    first_name
    case
        when points > 3000 then 'Gold'
        when points > 2000 then 'Silver'
        else 'Bronze'
   end as reward_status
from customers

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

You may use the alternative syntax for CASE expressions here:

SELECT
    customer_id,
    first_name,
    CASE WHEN points > 3000 THEN 'Gold'
         WHEN points BETWEEN 2000 to 3000 THEN 'Silver'
         ELSE 'Bronze' END AS rewards_status
FROM customers;

Upvotes: 0

Related Questions