How to count users per group assuming many-to-many relation?

Viewed 66

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`; 
3 Answers
Related