How to use comparison operator in case statement in MySQL?

Question

This is my table data:

id | days
-----------
1  | 10
2  | 20
3  | 30
4  | 25
5  | 5

I want the result like this:

id  days    resultcolumn
1    10     less than 10
2    20     less than 20
3    30     less than 30
4    25     less than 30
5    5      less than 10

I have used the following query:

SELECT id,days,
  CASE days 
    WHEN days<=10 THEN 'less than 10'
    WHEN days<=20 THEN 'less than 20'
    WHEN days<=30 THEN 'less than 30'
  END AS 'days2'
FROM calender

But I got the resulting data like this:

id  days    resultcolumn
1    10       {null}
2    20       {null}
3    30       {null}
4    25       {null}
5    5        {null}

How can I get my original result with the CASE statement in MySQL?

Answer 1

Try this:

SELECT
      id,
      days,
      CASE WHEN days<=10 THEN 'less than 10'
           WHEN days<=20 THEN 'less than 20'
           WHEN days<=30 THEN 'less than 30' END AS 'days2'
    FROM calender

Answer 2

You just have to remove days after CASE:

SELECT
  id,
  days,
  CASE WHEN days<=10 THEN 'less than 10'
       WHEN days<=20 THEN 'less than 20'
       WHEN days<=30 THEN 'less than 30' END AS 'days2'
FROM calender

Answer 3

You can do it without CASE ... WHEN construct. Just use simple math.

SELECT *, 
    CONCAT('less than ', CEIL(`days`/10)*10) AS `resultcolumn` 
FROM `Table1`

sqlfiddle

This prevents Branch predication from failing. Hence it'll be faster for large data set.

Answer 4

Try this:

SELECT id,days,
  CASE  
      WHEN days<=10 THEN 'less than 10'
      WHEN days<=20 THEN 'less than 20'
      WHEN days<=30 THEN 'less than 30'
  END AS 'resultcolumn'
FROM calender