0

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;
2
  • please tag your rdms and add some data to your query
    – nbk
    Jul 15, 2020 at 15:57
  • Did you actually populate your new column with values?
    – mustaccio
    Jul 15, 2020 at 16:44

2 Answers 2

0

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;
3
  • Hi Lennart! Thank you for the answer and it worked well! May I know what does the "AS t" part mean?
    – Lynn
    Jul 15, 2020 at 16:51
  • It's an alias for the derived table. It is not mandatory in the standard but some DBMS requires a name Jul 15, 2020 at 16:53
  • Ohh I see. Appreciate it!
    – Lynn
    Jul 15, 2020 at 17:03
0

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

2
  • Hi! I'm very new to SQL and can you please explain a little more on the VARCHAR()?
    – Lynn
    Jul 15, 2020 at 16:54
  • varchar is a datatype for characters and text, you can also use nvarchar or TXT, that dioesn't matter in zthe query. the user variable @capacity_diff is set before you make your CASE
    – nbk
    Jul 15, 2020 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.