Between any of the groups most relationships?

Question

I have 3 tables:

Create table users (
    id Number NOT NULL ,
        groupID Number NOT NULL ,
    name Varchar2(50),
primary key (id) 
);

Create table friends (
    friendID Number NOT NULL ,
    user_a Varchar2(35),
    user_b Varchar2(35),
primary key (friendID) 
);

Create table groups (
    groupID Number NOT NULL ,
    gname Varchar2(35),
primary key (groupID) 
);

Alter table users add  foreign key (groupID) references groups (groupID);

embedded data:

Users table
id | groupid | name
--------------------
1  |    1    |   s1
2  |    3    |   s2
3  |    2    |   s3
4  |    1    |   s4
5  |    2    |   s5

friends table
friendID | user_a | user_b
--------------------------
1        |   1    |   2
2        |   3    |   1
3        |   4    |   2
4        |   5    |   3
5        |   2    |   5

groups table 
groupid | groupname
--------------------
   1    |     in01
   2    |     in02
   3    |     in03

I want to run this query: Between any of the groups most relationships? This is my current query, but it doesn't work

SELECT g.gname ,count(a.aname) as con
FROM users a
left join group g on g.groupid=a.groupid
left join friends f on f.user_a=a.id
group by con;

Answer 1

How about:

Select gname1, gname2, count(*) cnt
    from (
Select least(g1.gname, g2.gname) gname1,
         Greatest(g1.gname, g2.gname) gname2
   From friends f
   Join users u1 on f.user_a = u1.id
   Join users u2 on f.user_b = u2.id
   Join groups g1 on g1.groupid = u1.groupid
   Join groups g2 on g2.groupid = u2.groupid
) gc
Group by gname1, gname2
Order by count(*) desc

The least and greatest function in the sub query are used to avoid having two entries for g1,g2 and g2,g1.

This will give you the counts of relationships between groups. Note, it'll include relationships between the same groups, if you don't want that you can always add a where clause

 Where u1.groupid != u2.groupid