CODEWITHSUNDEEP
mysqlsql
user199354
user199354

Reputation: 505

Top collaborator MySQL

I have a dataset where all authors associated with book are listed like below

| Book ID | Author |
|---------|--------|
| 1       | A      |
| 1       | B      |
| 1       | C      |
| 2       | A      |
| 2       | X      |
| 3       | P      |
| 3       | C      |
| 4       | Q      |
| 4       | B      |

And I want to find out which author is top collaborator, means worked with different authors. Like in above case

A has worked with B, C, X

B -> A, C, Q
C -> A, B, P
P -> C
X -> A
Q -> B

I tried few combinations of group concat but desired results are not coming

Upvotes: 2

Views: 67

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

One method is to use self-join to get the collaborators. Then you can use count(distinct) to count them:

select ba.author, count(distinct ba2.author) as num_collaborators,
       group_concat(distinct ba2.author0 as collaborators
from book_authors ba join
     book_authors ba2
     on ba.book_id = ba2.book_id and ba.author <> ba2.author
group by ba.author
order by num_collaborators desc;

Upvotes: 1

Related Questions