By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.36 |
CREATE TABLE cast (
cast_characters VARCHAR(512),
cast_identities VARCHAR(512),
cast_roles VARCHAR(512)
);
INSERT INTO cast (cast_characters, cast_identities, cast_roles) VALUES
('Barry', 'William', 'Hero'),
('Barry', 'William', 'Hero'),
('Barry', 'Scott', 'Hero'),
('Barry', 'Scott', 'Hero'),
('Alice', 'Susan', 'Villain'),
('Jerry', 'Smith', 'Villain'),
('Jerry', 'Smith', 'Villain'),
('Carlos', 'Salvador', 'Supporting'),
('Carlos', 'Salvador', 'Supporting');
SELECT * FROM cast
Records: 9 Duplicates: 0 Warnings: 0
| cast_characters | cast_identities | cast_roles |
|---|---|---|
| Barry | William | Hero |
| Barry | William | Hero |
| Barry | Scott | Hero |
| Barry | Scott | Hero |
| Alice | Susan | Villain |
| Jerry | Smith | Villain |
| Jerry | Smith | Villain |
| Carlos | Salvador | Supporting |
| Carlos | Salvador | Supporting |
SELECT cast_roles, CONCAT(cast_characters, cast_identities, cast_roles) as CONCAT3
FROM cast
| cast_roles | CONCAT3 |
|---|---|
| Hero | BarryWilliamHero |
| Hero | BarryWilliamHero |
| Hero | BarryScottHero |
| Hero | BarryScottHero |
| Villain | AliceSusanVillain |
| Villain | JerrySmithVillain |
| Villain | JerrySmithVillain |
| Supporting | CarlosSalvadorSupporting |
| Supporting | CarlosSalvadorSupporting |
SELECT cast_roles, COUNT(DISTINCT CONCAT(cast_characters, cast_identities, cast_roles)) AS 'cnt',
GROUP_CONCAT(CONCAT(cast_characters, cast_identities, cast_roles)) as concats
FROM cast
GROUP BY cast_roles
| cast_roles | cnt | concats |
|---|---|---|
| Hero | 2 | BarryWilliamHero,BarryWilliamHero,BarryScottHero,BarryScottHero |
| Supporting | 1 | CarlosSalvadorSupporting,CarlosSalvadorSupporting |
| Villain | 2 | AliceSusanVillain,JerrySmithVillain,JerrySmithVillain |
SELECT cast_roles, COUNT(DISTINCT cast_characters, cast_identities) as cnt
FROM
(
SELECT *
FROM cast
GROUP BY cast_characters, cast_identities, cast_roles
HAVING COUNT(*) > 1
) t
GROUP BY cast_roles
| cast_roles | cnt |
|---|---|
| Hero | 2 |
| Supporting | 1 |
| Villain | 1 |