MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DROP TABLE IF EXISTS `T1`;
CREATE TABLE IF NOT EXISTS `T1` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `TypeID` smallint DEFAULT NULL,
  `GroupName` varchar(16) NOT NULL DEFAULT 'N/A',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

INSERT INTO `T1` (`id`,`TypeID`,`GroupName`) VALUES
(1,1,'GName01'),
(2,1,'GName02');

Records: 2  Duplicates: 0  Warnings: 0


SELECT *
FROM T1;


id
TypeID
GroupName


1
1
GName01

2
1
GName02


DROP TABLE IF EXISTS `T2`;
CREATE TABLE IF NOT EXISTS `T2` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `TypeID` smallint DEFAULT NULL,
  `GroupID` smallint NOT NULL DEFAULT '0',
  `PersonID` smallint DEFAULT NULL,
  `PersonName` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

INSERT INTO `T2` (`id`,`TypeID`,`GroupID`,`PersonID`,`PersonName`) VALUES
(1,1,1,1,'Name01'),
(2,1,1,2,'Name02'),
(3,1,1,3,'Name03'),
(4,1,1,4,'Name04'),
(5,1,2,1,'Name05'),
(6,1,2,2,'Name06'),
(7,1,2,3,'Name07'),
(8,1,2,4,'Name08');

Records: 8  Duplicates: 0  Warnings: 0


SELECT *
FROM T2;


id
TypeID
GroupID
PersonID
PersonName


1
1
1
1
Name01

2
1
1
2
Name02

3
1
1
3
Name03

4
1
1
4
Name04

5
1
2
1
Name05

6
1
2
2
Name06

7
1
2
3
Name07

8
1
2
4
Name08


DROP TABLE IF EXISTS `T3`;
CREATE TABLE IF NOT EXISTS `T3` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `TypeID` smallint DEFAULT NULL,
  `GroupID` smallint NOT NULL DEFAULT '0',
  `Element1ID` smallint DEFAULT NULL,
  `Element2ID` smallint DEFAULT NULL,
  `Element3ID` smallint DEFAULT NULL,
  `Element1Name` varchar(16) DEFAULT NULL,
  `Element2Name` varchar(16) DEFAULT NULL,
  `Element3Name` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

INSERT INTO `T3` (`id`,`TypeID`,`GroupID`,`Element1ID`,`Element2ID`,`Element3ID`,`Element1Name`,`Element2Name`,`Element3Name`) VALUES
(1,1,1,NULL,NULL,NULL,NULL,NULL,NULL),
(2,1,1,NULL,NULL,NULL,NULL,NULL,NULL),
(3,1,1,1,NULL,NULL,'Name01',NULL,NULL),
(4,1,1,1,2,NULL,'Name01','Name02',NULL),
(5,1,1,2,3,4,'Name02','Name03','Name04'),
(6,1,1,2,4,NULL,'Name02','Name04',NULL),
(7,1,1,1,3,4,'Name01','Name03','Name04'),
(8,1,1,4,NULL,NULL,'Name04',NULL,NULL),
(9,1,2,NULL,NULL,NULL,NULL,NULL,NULL),
(10,1,2,3,4,NULL,'Name07','Name08',NULL),
(11,1,2,3,NULL,NULL,'Name07',NULL,NULL);

Records: 11  Duplicates: 0  Warnings: 0


SELECT *
FROM T3;


id
TypeID
GroupID
Element1ID
Element2ID
Element3ID
Element1Name
Element2Name
Element3Name


1
1
1
null
null
null
null
null
null

2
1
1
null
null
null
null
null
null

3
1
1
1
null
null
Name01
null
null

4
1
1
1
2
null
Name01
Name02
null

5
1
1
2
3
4
Name02
Name03
Name04

6
1
1
2
4
null
Name02
Name04
null

7
1
1
1
3
4
Name01
Name03
Name04

8
1
1
4
null
null
Name04
null
null

9
1
2
null
null
null
null
null
null

10
1
2
3
4
null
Name07
Name08
null

11
1
2
3
null
null
Name07
null
null


/*
how many "hits" get members/persons in each group

|TypeID|GroupID	|PersonID	|PersonName	|Hits	|
|1		|1		|1			|Name01		|3		|
|1		|1		|2			|Name02		|3		|
|1		|1		|2			|Name03		|2		|
|1		|1		|4			|Name04		|4		|
|1		|2		|3			|Name07		|2		|
|1		|2		|4			|Name08		|1		|
*/
SELECT ID, TypeID, GroupID, Element1ID AS PersonID, Element1Name AS PersonName, 1 AS Element FROM T3 WHERE Element1ID IS NOT NULL
UNION ALL
SELECT ID, TypeID, GroupID, Element2ID AS PersonID, Element2Name AS PersonName, 2 AS Element FROM T3 WHERE Element2ID IS NOT NULL
UNION ALL
SELECT ID, TypeID, GroupID, Element3ID AS PersonID, Element3Name AS PersonName, 3 AS Element FROM T3 WHERE Element3ID IS NOT NULL;


ID
TypeID
GroupID
PersonID
PersonName
Element


3
1
1
1
Name01
1

4
1
1
1
Name01
1

5
1
1
2
Name02
1

6
1
1
2
Name02
1

7
1
1
1
Name01
1

8
1
1
4
Name04
1

10
1
2
3
Name07
1

11
1
2
3
Name07
1

4
1
1
2
Name02
2

5
1
1
3
Name03
2

6
1
1
4
Name04
2

7
1
1
3
Name03
2

10
1
2
4
Name08
2

5
1
1
4
Name04
3

7
1
1
4
Name04
3


WITH T4 AS
(
  SELECT ID, TypeID, GroupID, Element1ID AS PersonID, Element1Name AS PersonName, 1 AS Element FROM T3 WHERE Element1ID IS NOT NULL
  UNION ALL
  SELECT ID, TypeID, GroupID, Element2ID AS PersonID, Element2Name AS PersonName, 2 AS Element FROM T3 WHERE Element2ID IS NOT NULL
  UNION ALL
  SELECT ID, TypeID, GroupID, Element3ID AS PersonID, Element3Name AS PersonName, 3 AS Element FROM T3 WHERE Element3ID IS NOT NULL
)
SELECT TypeID, GroupID, PersonID, PersonName, COUNT(*) AS Hits
FROM T4
GROUP BY TypeID, GroupID, PersonID, PersonName
ORDER BY TypeID, GroupID, PersonID;


TypeID
GroupID
PersonID
PersonName
Hits


1
1
1
Name01
3

1
1
2
Name02
3

1
1
3
Name03
2

1
1
4
Name04
4

1
2
3
Name07
2

1
2
4
Name08
1


/*
how many "hits" get each group

|TypeID	|GroupID|GroupName|Hits	|
|1		|1		|GName01	|8	|
|1		|2		|GName02	|3	|
*/

id	TypeID	GroupID	Element1ID	Element2ID	Element3ID	Element1Name	Element2Name	Element3Name
1	1	1	null	null	null	null	null	null
2	1	1	null	null	null	null	null	null
3	1	1	1	null	null	Name01	null	null
4	1	1	1	2	null	Name01	Name02	null
5	1	1	2	3	4	Name02	Name03	Name04
6	1	1	2	4	null	Name02	Name04	null
7	1	1	1	3	4	Name01	Name03	Name04
8	1	1	4	null	null	Name04	null	null
9	1	2	null	null	null	null	null	null
10	1	2	3	4	null	Name07	Name08	null
11	1	2	3	null	null	Name07	null	null