How many records for group and how many records for group members?

Other SQL Server Topics
1

hi,
three db-tables, first T1 table for groups information:

> T1:
> |id|TypeID|GroupName|
> |1 |1		|GName01	|
> |2 |1		|GName02	|

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');

second T2 table for members (or persons) of each group:

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 `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');

and third T3 table for evidence of hits - hit (or record) can get some of group (without identify any member - check records 1,2 or 9), or hit can get some group with member identification (one, two or three members maximum - check records 3,4,5,6,7,8,10,11):

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

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);
  • I need SQL-command, SELECT for retrieve how many "hits" get members/persons in each group - output would be like this:
|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		|
  • and next how many "hits" get each group - output would be like this:
|TypeID	|GroupID|GroupName|Hits	|
|1		|1		|GName01	|8	|
|1		|2		|GName02	|3	|

please who can help me?

very thanks in advance

2

First, you have to "unpivot" T3, then you can do a simple GROUP BY on it.

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;

I'll leave it to you from there to figure out the hits per group. It doesn't make sense that group 1 should be 8.

3

hi SqlHippo, great tip, thanks.
Set of "UNIONs" returnes usefull output:

|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			|Name07		|3		|

but whole SQL command (in my MySQL):

WITH T4 AS
( ... UNIONs ...) 
SELECT TypeID, GroupID, PersonID, PersonName, COUNT(*) AS Hits
FROM T4
GROUP BY TypeID, GroupID, PersonID, PersonName
ORDER BY TypeID, GroupID, PersonID;

returns back - no resultset.
Is something wrong?

4

I couldn't even begin to guess why that wouldn't work for you. It worked in the dbfiddle I linked.