0

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

RusArtM
  • 1,116
  • 3
  • 15
  • 22

2 Answers2

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

Nick
  • 138,499
  • 22
  • 57
  • 95
  • 1
    Can you somehow generate the list by querying metadata? – Boris the Spider Jun 03 '20 at 05:24
  • 1
    @BoristheSpider Of course this is possible (for example you may query and parse INFORMATION_SCHEMA.COLUMNS.COLUMN_TYPE). – Akina Jun 03 '20 at 05:38
  • 1
    @BoristheSpider see https://stackoverflow.com/questions/2350052/how-can-i-get-enum-possible-values-in-a-mysql-database 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. – Nick Jun 03 '20 at 05:42
  • *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. – Akina Jun 03 '20 at 06:16
  • @Akina I'm not saying it's not unjustifiable, or that administrators are lazy, but it has been my experience... – Nick Jun 03 '20 at 06:18
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;
RusArtM
  • 1,116
  • 3
  • 15
  • 22