How to get enum field value by its index in mysql

Question

I have a table with an enum field. I need to calculate average value of this field and show it as one of enum elements.

I'm getting the average like this: SELECT AVG(val) FROM test; It works fine. But then I need to get one of enum values back by this index. To get index I'm using ROUND. But I don't know what to do next.

Is it possible?

Is there some other approach?

Example

Create a table:

CREATE TABLE test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    val ENUM('bad', 'good', 'perfect')
);

Fill the table:

INSERT INTO test(val) VALUES('bad'), ('bad'), ('good'), ('bad'), ('perfect'), ('perfect'), ('good');

Select query to be fixed somehow:

SELECT AVG(val) average_value_ind, ROUND(AVG(val)) closest_value_ind FROM test;

In this example the average value should be good

Another approach? Yes; don't use ENUM.

Strawberry
– Strawberry

2020-06-03 04:57:34 +00:00
Commented Jun 3, 2020 at 4:57 — Strawberry
– Strawberry, Commented Jun 3, 2020 at 4:57
I'm not responsible for this table and can't change it

RusArtM
– RusArtM

2020-06-03 06:24:26 +00:00
Commented Jun 3, 2020 at 6:24 — RusArtM
– RusArtM, Commented Jun 3, 2020 at 6:24

Nick · Accepted Answer · 2020-06-03 05:10:30Z

2

While it's easy to convert ENUM values into integers or strings, it's harder to go the other way. You can use the ELT function but that requires enumerating all the ENUM values:

SELECT AVG(val) average_value_ind, 
       ELT(ROUND(AVG(val)), 'bad', 'good', 'perfect') closest_value_ind 
FROM test;

Output:

average_value_ind   closest_value_ind
1.8571428571428572  good

Demo on dbfiddle

answered Jun 3, 2020 at 5:10

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Boris the Spider Over a year ago

Can you somehow generate the list by querying metadata?

Akina Over a year ago

@BoristheSpider Of course this is possible (for example you may query and parse INFORMATION_SCHEMA.COLUMNS.COLUMN_TYPE).

Nick Over a year ago

@BoristheSpider see stackoverflow.com/questions/2350052/… for some possible solutions. In theory you could join information_schema.COLUMNS to get the information but in many instances users don't have access to those tables.

Akina Over a year ago

in many instances users don't have access to those tables This is strange and unjustifiable restriction... except for the laziness of the administrator, who is easier to deny access to the INFORMATION_SCHEMA at all than to give the privilege for SELECT only to INFORMATION_SCHEMA which is a couple of views, not tables.

Nick Over a year ago

@Akina I'm not saying it's not unjustifiable, or that administrators are lazy, but it has been my experience...

RusArtM · Accepted Answer · 2020-06-03 06:38:16Z

1

Thank you all for your answers and comments. You've helped me.

Here what I've managed to do. It works in my case.

SELECT 
    AVG(val) average_value, 
    ROUND(AVG(val)) closest_value_id, 
    JSON_UNQUOTE(JSON_EXTRACT(val_enum.items, CONCAT("$[", ROUND(AVG(val)) - 1, "]"))) enum_value
FROM test
CROSS JOIN (
    SELECT CAST(CONCAT('[', REPLACE(SUBSTRING(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE) - 6), "'", '"'), ']') AS JSON) items
    FROM information_schema.COLUMNS 
    WHERE TABLE_NAME = 'test' AND COLUMN_NAME = 'val'
) val_enum;

answered Jun 3, 2020 at 6:38

RusArtM

1,3703 gold badges18 silver badges29 bronze badges

3 Comments

Akina Over a year ago

Check SCHEMA_NAME in the subquery too for to avoid that there exists the table test with the column val in another database.

Nick Over a year ago

That is horrible and yet strangely beautiful at the same time... :-)

RusArtM Over a year ago

@Nick yes, I wish there would be a better way to do this ;)

Collectives™ on Stack Overflow

How to get enum field value by its index in mysql

Example

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Example

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related