I'm doing a pokemon sql table and created a new column named capacity_difference to determine the collectability of each pokemon in the table. However, when I want to use CASE query to categorize them, the outcomes only show the ELSE condition for every log. Can someone please tell me how to fix the query?
My queries are as follow:
ALTER TABLE pokemon add capacity_difference INTEGER;
SELECT name, type_1, type_2, HP, (attack - defense) as capacity_difference from pokemon;
SELECT name, type_1, type_2, HP, (attack - defense) as capacity_difference,
CASE
when capacity_difference > 90 then "collect asap"
when capacity_difference < 90 and capacity_difference > 50 then "good"
when capacity_difference > 0 and capacity_difference < 50 then "okay"
when capacity_difference > -10 and capacity_difference < 0 then "bad"
ELSE "worse"
END AS Collectability
FROM pokemon;