Case

MySQL Statement: Flow Control Case
Categories:
Tags:

Syntax

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  WHEN conditionN THEN resultN
  ELSE result
END;

Example

Single Value

CASE
  WHEN City IS NULL THEN Country
  ELSE City
END

SELECT
  CASE level
    WHEN 'A' THEN 'Beginner'
    WHEN 'B' THEN 'Junior'
    WHEN 'C' THEN 'Senior'
    ELSE 'Level doesn`t exist!'
  END AS experience_levels
FROM company;

Multiple Value

GROUP BY 
  CASE 
    WHEN org.size IN (0, 1) THEN '<26'
    WHEN org.size = 2 THEN '26-50'
    WHEN org.size = 3 THEN '51-100'
    WHEN org.size = 4 THEN '101-250'
    WHEN org.size = 5 THEN '251-500'
    WHEN org.size = 6 THEN '501-1000'
    WHEN org.size = 7 THEN '1001-5000'
    ELSE '5000+' 
  END

Multiple Condition

SELECT
  CASE
    WHEN points < 10 THEN 'failure'
    WHEN points BETWEEN 10 AND 12 THEN 'passable'
    WHEN points BETWEEN 12 AND 16 THEN 'good'
    ELSE 'very good'
  END AS result
FROM test;

Count value by use CASE and SUM

SELECT 
    count(id), 
    SUM(hour) as toHour, 
    SUM(CASE WHEN kind = 1 THEN 1 ELSE 0 END) as countKind1,
    SUM(CASE WHEN kind = 2 THEN 1 ELSE 0 END) as countKind2
FROM sometable
ORDER BY
  countKind1 DESC,
  countKind2 ASC

Reference