I have two tables users and groups, as well as a third table that defines many-to-many relationship users_groups:
Table "group":
CREATE TABLE `group` (
`ID` int(11) NOT NULL,
`creation_date` date NOT NULL,
`type` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `group` (`ID`, `creation_date`, `type`) VALUES
(1, '2017-06-01', 'AAA'),
(2, '2017-06-01', 'BBB'),
(3, '2017-06-01', 'CCC');
Table "user":
CREATE TABLE `user` (
`ID` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`surnames` varchar(100) NOT NULL,
`birthdate` date NOT NULL,
`qualification` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `user` (`ID`, `name`, `surnames`, `birthdate`, `qualification`) VALUES
(1, 'xxx', 'xxx', '1989-03-03', 'A'),
(2, 'xxx', 'xxx', '1990-06-15', 'A'),
(3, 'xxx', 'xxx', '1982-06-14', 'B'),
(4, 'xxx', 'xxx', '1988-06-19', 'B'),
(5, 'xxx', 'xxx', '1984-03-12', 'B'),
(6, 'xxx', 'xxx', '1987-12-19', 'C');
Table "users_groups":
CREATE TABLE `user_group` (
`ID` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`group_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `user_group` (`ID`, `user_id`, `group_id`) VALUES
(1, 1, 1),
(2, 2, 1),
(3, 3, 2),
(4, 4, 2),
(5, 5, 2),
(6, 6, 3);
I need to count the number of users per group and summarize results as follows:
ID creation_date type users_count_per_group
1 2017-06-01 AAA 2
2 2017-06-01 BBB 3
3 2017-06-01 CCC 1
This is my current query. I get stuck with many-to-many relation, which means that the same user may appear in different groups (though in my sample data all users appear just once per group):
SELECT `group`.`ID`, `group`.`creation_date`, `group`.`type`, COUNT(*)
FROM `group`, `user`, `user_group`
WHERE `group`.`ID`=`user_group`.`group_id`
AND `user`.`ID`=`user_group`.`user_id`;