title: Oracle, inner join with group by clause
body: <p>I have two oracle tables :</p>
  <blockquote>
  <p>USERS : ID, USERNAME, PASSWORD, ROLE</p>
  <p>ARCHIVE_POSESSION : ID, USERID, ARCHIVEID</p>
  </blockquote>
  <p>What I'm trying to do is obtain a query that returns me the following result set :</p>
  <blockquote>
  <p>Username, Role, Number Of Archives</p>
  </blockquote>
  <p>So far so good, but I can't wrap my head around the query, I'm still a beginner. So far this is where I got:</p>
  <pre><code>SELECT users.ID, USERNAME, ROLE, count(archive_posession.USERID) 
  from users inner join 
  archive_posession on 
  users.id = archive_posession.USERID 
  group by 
  archive_posession.USERID ;
  </code></pre>
  <p>But it gives me this error</p>
  <pre><code>ORA-00979: not a GROUP BY expression
  </code></pre>
  <p>Any tips? I'm sure group by is supposed to work for aggregate functions, but in this case I'm stoked.</p>