Oracle, inner join with group by clause

Question

I have two oracle tables :

USERS : ID, USERNAME, PASSWORD, ROLE

ARCHIVE_POSESSION : ID, USERID, ARCHIVEID

What I'm trying to do is obtain a query that returns me the following result set :

Username, Role, Number Of Archives

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:

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 ;

But it gives me this error

ORA-00979: not a GROUP BY expression

Any tips? I'm sure group by is supposed to work for aggregate functions, but in this case I'm stoked.

Answer 1

The fields that you select are either be declared in group by or you should use an aggregate function. For example

SELECT archive_posession, Max(USERID USERNAME), Max(ROLE), count(archive_posession.USERID) 
FROM users INNER JOIN
archive_posession ON 
users.id = archive_posession.USERID 
GROUP BY 
archive_posession.USERID ;

should do the trick.

The reason why that resolves this issue is because Group By returns a single row. So you should find a way to gather all selected fields that are not in group by in a single row only

Answer 2

It looks to me like you're trying to do something like

SELECT u.USERNAME, u.ROLE, COUNT(a.USERID)
  FROM USERS u
  INNER JOIN ARCHIVE_POSESSION a
    ON a.USERID = u.ID
  GROUP BY u.USERNAME, u.ROLE;

All columns in the SELECT list of a GROUP BY query must either be members of the GROUP BY list, or must be an aggregate function such as COUNT. In your query USERNAME and ROLE were not members of the GROUP BY list, and thus the query you saw was produced.