Re: CASE with a divide 2 columns

[Rick James]

mysql> SELECT state, city, population,
    -> CASE WHEN population < 10000 THEN 'Small'
    -> WHEN population <100000 THEN 'Medium'
    -> ELSE 'Large' END AS Size
    -> FROM us ORDER BY RAND() LIMIT 11;
+-------+--------------+------------+--------+
| state | city         | population | Size   |
+-------+--------------+------------+--------+
| MN    | Ham Lake     |      14954 | Medium |
| ND    | Wahpeton     |       8395 | Small  |
| ID    | Sandpoint    |       7785 | Small  |
| MD    | Severna Park |      30292 | Medium |
| OH    | Zanesville   |      24976 | Medium |
| WI    | Superior     |      27130 | Medium |
| CA    | Ceres        |      40082 | Medium |
| CA    | Costa Mesa   |     109250 | Large  |
| MS    | Laurel       |      17891 | Medium |
| WV    | Morgantown   |      28957 | Medium |
| MA    | Rehoboth     |      11486 | Medium |
+-------+--------------+------------+--------+
11 rows in set (0.01 sec)

Or, in case you need a GROUP BY:

mysql> SELECT state, SUM(population) AS "City total",
    -> CASE WHEN SUM(population)< 1000000 THEN 'Small'
    -> WHEN SUM(population)<10000000 THEN 'Medium'
    -> ELSE 'Large' END AS Size
    -> FROM us
    -> GROUP BY state
    -> ORDER BY RAND() LIMIT 11;
+-------+------------+--------+
| state | City total | Size   |
+-------+------------+--------+
| FL    |   10571845 | Large  |
| MT    |     415328 | Small  |
| NH    |    1060359 | Medium |
| DC    |     552433 | Small  |
| MI    |    4725467 | Medium |
| ME    |     808069 | Small  |
| KY    |    1343164 | Medium |
| NE    |     979290 | Small  |
| NV    |    2015956 | Medium |
| IL    |    9219107 | Medium |
| PA    |    3787840 | Medium |
+-------+------------+--------+
11 rows in set (0.02 sec)

A subquery could be used to avoid the repeated SUM()s, but it is not worth the effort.