For each string in column calculate average (second column)

Question

This is a section of my dataset (for example flavours of ice cream and their ratings):

flavor	rating
cherry, apple, flower	4.0
apple, chocolate, banana	3.0
banane, chocolate, strawberry	4.0
cherry, banane, strawberry	1.0

Now I want to calculate the average rating of the flavours. So that I get following output:

flavor	avg(rating)
cherry	2.5
apple	3.5
banane	2.66
strawberry	2.5
chocolate	3.5
flower	4.0

How can I approach this in mysql?

Normalize your schema. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) (Spoiler: Yes, it is.). — sticky bit, Dec 22 '21 at 14:50
Not an exact duplicate, but the big issue here would be to split the data into separate rows, as answered in https://stackoverflow.com/q/17942508/2422776 . From there on, you can group by the flavor and query the average. — Mureinik, Dec 22 '21 at 14:53

score 1 · Answer 1 · answered Dec 22 '21 at 15:46

with recursive u as
(select 1 as n
union all select n + 1 from u
where n < (select max(length(flavor) - length(replace(flavor, ',', ''))) + 1 
from ice_cream)),
v as
(select
  LTRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(ice_cream.flavor, ',', u.n), ',', -1)) 
single_flavor,
ice_cream.rating
from
  u inner join ice_cream
  on CHAR_LENGTH(ice_cream.flavor)
  -CHAR_LENGTH(REPLACE(ice_cream.flavor, ',', ''))>=u.n-1
)
(select single_flavor, avg(rating) from v group by single_flavor);

u gets you a table with the numbers 1, 2, ..., max number of single flavours in a row. v makes use of u to split every row in the original table into single flavours and their rating, and the last cte just groups rows from v by single flavour and calculates the average rating for each flavour.

Fiddle

Oops, I didn't see that last sentence. Anyway, I have now approved your edit. — jarlh, Dec 22 '21 at 15:57

score 0 · Answer 2 · answered Dec 22 '21 at 15:42

You can use the following query

select flavor,avg(rating)
from 
 
  (select
    tablename.rating,
    SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.flavor, ',', numbers.n), ',', -1) flavor
  from
    (select 1 n union all
     select 2 union all select 3 union all
     select 4 union all select 5) numbers INNER JOIN tablename
    on CHAR_LENGTH(tablename.flavor)
       -CHAR_LENGTH(REPLACE(tablename.flavor, ',', ''))>=numbers.n-1) t

group by flavor

Demo in db<>fiddle

Take a look at this link SQL split values to multiple rows

For each string in column calculate average (second column)

2 Answers2