MySQL CASE WHEN with Calculation and SELECT Statement

07/26/2011
| No Comments

Here is an simple CASE WHEN with calculations:

SELECT CASE 10*2
WHEN 20 THEN '20 correct'
WHEN 30 THEN '30 correct'
WHEN 40 THEN '40 correct'
END;

Here is another sample CASE WHEN statement in SELECT statement:

mysql> SELECT Name, RatingID AS Rating,
->    CASE RatingID
->       WHEN 'R' THEN 'Under 17 requires an adult.'
->       WHEN 'X' THEN 'No one 17 and under.'
->       WHEN 'NR' THEN 'Use discretion when renting.'
->       ELSE 'OK to rent to minors.'
->    END AS Policy
-> FROM DVDs
-> ORDER BY Name;
+-----------+--------+------------------------------+
| Name      | Rating | Policy                       |
+-----------+--------+------------------------------+
| Africa    | PG     | OK to rent to minors.        |
| Amadeus   | PG     | OK to rent to minors.        |
| Christmas | NR     | Use discretion when renting. |
| Doc       | G      | OK to rent to minors.        |
| Falcon    | NR     | Use discretion when renting. |
| Mash      | R      | Under 17 requires an adult.  |
| Show      | NR     | Use discretion when renting. |
| View      | NR     | Use discretion when renting. |
+-----------+--------+------------------------------+
8 rows in set (0.01 sec)

MySQL
, ,