title: Counting inner joined query
body: <p>I have two tables one with 'Avatars' and one with 'Children' the children table holds values of parent avatar ID and of child avatar ID. I'm trying to get from the table a list of avatars that have more than 2 children.</p>
  
  <p>Tables:</p>
  
  <pre><code>CREATE TABLE avatars
      (avatarID NUMBER CONSTRAINT pk_avatars PRIMARY KEY,
      username VARCHAR2(20),
      gender CHAR(1),
      );
  
  CREATE TABLE children
      (childID NUMBER CONSTRAINT pk_children PRIMARY KEY,
      childAvatarID NUMBER,
      parentAvatarID NUMBER,
      mainParent NUMBER(1,0)
      );
  </code></pre>
  
  <p>Relationships:</p>
  
  <pre><code>ALTER TABLE children
  ADD FOREIGN KEY (parentAvatarID)
  REFERENCES avatars(avatarID)
  ON DELETE CASCADE;
  
  ALTER TABLE children
  ADD FOREIGN KEY (childAvatarID)
  REFERENCES avatars(avatarID)
  ON DELETE CASCADE;
  </code></pre>
  
  <p>My query:</p>
  
  <pre><code>SELECT count(children.avatarid), avatars.username
  FROM avatars
  INNER JOIN (
    SELECT *
    FROM avatars
    INNER JOIN children ON avatars.avatarID = children.childAvatarID
    WHERE avatars.gender = 'M'
  ) children ON avatars.avatarID = children.parentavatarID
  WHERE avatars.gender = 'M'
  GROUP By children.avatarid, avatars.username;
  </code></pre>
  
  <p>It shows for count(children.avatarid) always 1 no matter how many children an avatar has.</p>