SQL CASE query prioritization

Question

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;

Answer 1

The problem is that renaming ( (attack - defense) as capacity_difference ) is not known at the time when your case expression is evaluated. I.e. Your case expression is evaluating the column capacity_difference which you have not assigned a value. I would suggest that you get rid of that column, and do:

SELECT name
     , type_1
     , type_2
     , HP
     , 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 (
    SELECT (attack - defense) as capacity_difference
         ,  name
         , type_1
         , type_2
         , HP
    FROM pokemon
) AS t;

Answer 2

With mysql and mariadb, you can do

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          

db<>fiddle here