title: How to count correctly 2 inner levels count?
body: <p>I have a structure for a university DB wherein I have three tables: <code>room</code>, <code>students</code>, <code>possessions</code></p>
  <pre><code>CREATE TABLE `rooms` (
      `id` INT(10) NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(50) NOT NULL COLLATE 'utf8_general_ci',
      PRIMARY KEY (`id`) USING BTREE,
      UNIQUE INDEX `name` (`name`) USING BTREE
  )
  COLLATE='utf8_general_ci'
  ENGINE=InnoDB
  AUTO_INCREMENT=3
  ;
  
  CREATE TABLE `students` (
      `id` INT(10) NOT NULL AUTO_INCREMENT,
      `ref_room_id` INT(10) NULL DEFAULT NULL,
      `student_name` VARCHAR(50) NOT NULL COLLATE 'utf8_general_ci',
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `FK_students_room` (`ref_room_id`) USING BTREE,
      CONSTRAINT `FK_students_room` FOREIGN KEY (`ref_room_id`) REFERENCES `university`.`rooms` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
  )
  COLLATE='utf8_general_ci'
  ENGINE=InnoDB
  AUTO_INCREMENT=6
  ;
  
  CREATE TABLE `possessions` (
      `id` INT(10) NOT NULL AUTO_INCREMENT,
      `ref_student_id` INT(10) NOT NULL,
      `name` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `FK__students` (`ref_student_id`) USING BTREE,
      CONSTRAINT `FK__students` FOREIGN KEY (`ref_student_id`) REFERENCES `university`.`students` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
  )
  COLLATE='utf8_general_ci'
  ENGINE=InnoDB
  AUTO_INCREMENT=4
  ;
  
  INSERT INTO rooms (name) VALUES ('a'),('b');
  
  INSERT INTO students (`ref_room_id`, `student_name`) VALUES 
  (3,1),
  (3,2),
  (3,3),
  (3,4);
  
  INSERT INTO possessions (ref_student_id, name) VALUES 
  (7,'a')
  (7,'aa'),
  (7,'aaa'),
  (8,'aaaa'),
  (9,'aaaaa'),
  (6,'aaaaaa'),
  (7,'aaaaaaa');
  </code></pre>
  <p>So, in order to present such a table in MySQL, I created the procedure</p>
  <pre><code>CREATE DEFINER=`root`@`localhost` PROCEDURE `get_data`()
  LANGUAGE SQL
  NOT DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY DEFINER
  COMMENT ''
  BEGIN
  
  SELECT r.id AS room_id, r.name, COUNT(s.id) AS student_num, COUNT(p.id) AS possessions_num
  FROM rooms r 
  INNER JOIN students s ON s.ref_room_id = r.id
  INNER JOIN possessions p ON p.ref_student_id = s.id
  GROUP BY r.name;
  
  END
  </code></pre>
  <p>but what I get is</p>
  <p><a href="https://i.stack.imgur.com/hxK4O.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hxK4O.png" alt="enter image description here" /></a></p>
  <p>First of all, it misses the room and secondly instead of 4 students it shows 7...</p>
  <p>What am I doing wrong?</p>