SQL display low med high as 1 2 3-CodePudding

I'm very new to this so sorry if I struggle with clarity.

The database I am working from has a column of values stored as "very low, low, medium, high, very high". To try and do maths and visualization of these it would be much better if I had a corresponding column of numbers.

For example I have

Field	Disease	Rating
1	Root Rot	Low
2	Leaf Death	low
3	Root Rot	Med
4	Root Rot	Very High

and I'd like a new column with very low as zero, low as one and so on.

CodePudding user response：

I think that you could use the CASE command, for example:

SELECT id
     , disease
     , CASE WHEN rating = 'low' THEN 0 
            WHEN rating = 'mid' THEN 1 
            ELSE 'etc' 
       END AS rating
FROM mytable;

CodePudding user response：

You can make a temporary table to join on instead:

Schema (SQLite v3.30)

CREATE TABLE data (
  `Field` INTEGER,
  `Disease` VARCHAR(10),
  `Rating` VARCHAR(9)
);

INSERT INTO data
  (`Field`, `Disease`, `Rating`)
VALUES
  ('1', 'Root Rot', 'Low'),
  ('2', 'Leaf Death', 'Low'),
  ('3', 'Root Rot', 'Med'),
  ('4', 'Root Rot', 'Very High');

Query

WITH keys(Rating, Value) AS (
  SELECT * 
  FROM (
    VALUES 
    ('Low', 1),
    ('Med', 2),
    ('Very High', 3)
  )
)
SELECT *
FROM data
JOIN keys 
USING(Rating);

Field	Disease	Rating	Value
1	Root Rot	Low	1
2	Leaf Death	Low	1
3	Root Rot	Med	2
4	Root Rot	Very High	3

View on DB Fiddle

Formatting will vary depending on the Flavor of SQL, which you didn't provide.