By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CrEATE Table pokemon (name VARCHAR(19), type_1 VARCHAR(19), type_2 VARCHAR(19), HP INT, attack INT, defense INT);
INSERT INTO pokemon VALUES ('a','fire','water',20,4,2),('b','fire','water',20,67,2);
SELECT name, type_1, type_2, HP, @capacity_diff := (attack - defense) as capacity_difference,
CASE
when @capacity_diff > 90 then "collect asap"
when @capacity_diff < 90 and @capacity_diff > 50 then "good"
when @capacity_diff > 0 and @capacity_diff < 50 then "okay"
when @capacity_diff > -10 and @capacity_diff < 0 then "bad"
ELSE "worse"
END AS Collectability
FROM pokemon;
| name | type_1 | type_2 | HP | capacity_difference | Collectability |
|---|---|---|---|---|---|
| a | fire | water | 20 | 2 | okay |
| b | fire | water | 20 | 65 | good |