Re: GROUP_CONCAT and COUNT
Well, the structure and data of an example database are
CREATE TABLE `mt_films` (
`ID_films` int(11),
`Title1` varchar(150) DEFAULT NULL,
PRIMARY KEY (`ID_films`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (1,'SOPHIE');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (2,'THE TRUTH');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (3,'GEOSTORM');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (4,'THE FIRM');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (5,'GIANT');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (6,'AFTERLOVE');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (7,'ZUKOFSKY');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (8,'YEARS OF THE BIG HEAT');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (9,'WORK LATE');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (10,'BLADE RUNNER');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (11,'EXCORCIST');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (12,'DUST');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (13,'TOOTH');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (14,'FITNESS');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (15,'WONDERFUL DAYS');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (16,'WOMAN OF UNKNOWN IDENTITY');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (17,'WITHOUT MILK');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (18,'VERY POOR INC');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (19,'PRACTICE');
INSERT INTO `mt_films` (`ID_films`,`Title1`) VALUES (20,'PASSIONADES LOVERS');
CREATE TABLE `st_peoplefilms` (
`aa_st_peoplefilms` int(11) NOT NULL AUTO_INCREMENT,
`ID_films` int(11) NOT NULL,
`ID_person` int(11) NOT NULL,
`ID_idiotita` int(11) DEFAULT NULL,
PRIMARY KEY (`aa_st_peoplefilms`),
UNIQUE KEY `aa_st_peoplefilms_UNIQUE` (`aa_st_peoplefilms`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (1,1,1,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (2,2,1,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (3,3,1,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (4,4,2,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (5,5,2,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (6,6,1,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (7,7,1,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (8,8,3,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (9,9,4,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (10,10,5,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (11,11,6,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (12,12,7,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (13,13,2,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (14,14,2,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (15,15,2,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (16,16,3,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (17,17,1,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (18,18,1,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (19,19,11,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (20,20,4,29);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (21,1,1,28);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (22,2,1,27);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (23,1,1,15);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (24,4,2,10);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (25,5,2,28);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (26,6,1,16);
INSERT INTO `st_peoplefilms` (`aa_st_peoplefilms`,`ID_films`,`ID_person`,`ID_idiotita`) VALUES (27,7,1,39);
CREATE TABLE `t_people` (
`ID_person` int(11) NOT NULL AUTO_INCREMENT,
`person` varchar(100) NOT NULL,
`sex` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`ID_person`),
UNIQUE KEY `ID_person_UNIQUE` (`ID_person`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (1,'Antonis Antoniou',1);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (2,'Bella',2);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (3,'Jerry',1);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (4,'Marios',1);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (5,'Bruce',1);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (6,'Sophie',2);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (7,'Simon',1);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (8,'Vangelis',1);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (9,'Kostas',1);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (10,'Giorgos',1);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (11,'Yiannis',1);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (12,'Petroς',1);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (13,'Dirk',1);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (14,'Maria',2);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (15,'Peter',1);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (16,'George',1);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (17,'Nick',1);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (18,'Emmanuel',2);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (19,'Ramin',1);
INSERT INTO `t_people` (`ID_person`,`person`,`sex`) VALUES (20,'Helen',2);
The final query based on these data (where ID_idiotita=29 and sex=1) must show
----------------------------
person total
----------------------------
Antonis 7
Jerry 2
Marios 2
Simon, Yiannis, Bruce 1
----------------------------
thanks