By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.30 |
CREATE TABLE mytable (
element VARCHAR(20) ,
score VARCHAR(20) );
INSERT INTO mytable VALUES
("ABC","50"),
("BAC","10"),
("CBA","30"),
("XYZ","115"),
("ZXY","25");
Records: 5 Duplicates: 0 Warnings: 0
SELECT * FROM mytable;
| element | score |
|---|---|
| ABC | 50 |
| BAC | 10 |
| CBA | 30 |
| XYZ | 115 |
| ZXY | 25 |
-- split the strings (currently fixed to max 3 positions...)
SELECT
element,
score,
x,
substring(element,x,1) as Y
FROM mytable
CROSS JOIN (SELECT 1 as x union all select 2 union all select 3) x
| element | score | x | Y |
|---|---|---|---|
| ABC | 50 | 3 | C |
| ABC | 50 | 2 | B |
| ABC | 50 | 1 | A |
| BAC | 10 | 3 | C |
| BAC | 10 | 2 | A |
| BAC | 10 | 1 | B |
| CBA | 30 | 3 | A |
| CBA | 30 | 2 | B |
| CBA | 30 | 1 | C |
| XYZ | 115 | 3 | Z |
| XYZ | 115 | 2 | Y |
| XYZ | 115 | 1 | X |
| ZXY | 25 | 3 | Y |
| ZXY | 25 | 2 | X |
| ZXY | 25 | 1 | Z |
-- create element_new
SELECT
element,
score,
GROUP_CONCAT(y order by y SEPARATOR '') as element_new
FROM (
SELECT
element,
score,
x,
substring(element,x,1) as Y
FROM mytable
CROSS JOIN (SELECT 1 as x union all select 2 union all select 3) x
) Y
GROUP BY element,score;
| element | score | element_new |
|---|---|---|
| ABC | 50 | ABC |
| BAC | 10 | ABC |
| CBA | 30 | ABC |
| XYZ | 115 | XYZ |
| ZXY | 25 | XYZ |
-- do your thing
SELECT
element_new,
sum(score) as score
FROM (
SELECT
element,
score,
GROUP_CONCAT(y order by y SEPARATOR '') as element_new
FROM (
SELECT
element,
score,
x,
substring(element,x,1) as Y
FROM mytable
CROSS JOIN (SELECT 1 as x union all select 2 union all select 3) x
) Y
GROUP BY element,score
) z
GROUP BY element_new;
| element_new | score |
|---|---|
| ABC | 90 |
| XYZ | 140 |