CODEWITHSUNDEEP
sqlsqlite
BKS
BKS

Reputation: 2333

SQLite3 Select Statement Query

I have the following sqlite3 table, BookAuthor:

BookID    AuthorID
B13       A02
B13       A67
B13       A13
B02       A02
B02       A67
B45       A02
B45       A13
B33       A02

And want to produce this one:

Author1  Author2 NumCoAuthored
A02      A67     2
A02      A13     2
A67      A13     1

I need help writing the sql select statement. I don't even know where to start or if it's possible.

Kindly note that the data set is really large (more than 10 GB) and I'm guessing i might have to do it in chunk sizes.

So maybe I should order by AuthorID and do it each author at a time with every other Author?

Upvotes: 0

Views: 52

Answers (1)

Abecee
Abecee

Reputation: 2393

This should get you started:

SELECT
  BA1.authorID AS Author1
  , BA2.authorID AS Author2
  , COUNT(BA1.bookID) NumCoAuthored
FROM BookAuthor BA1
JOIN BookAuthor BA2
  ON BA1.bookID = BA2.bookID
  AND BA1.authorID < BA2.authorID
GROUP BY BA1.authorID, BA2.authorID

See it in action: SQL Fiddle.

Please comment if and as this requrires adjustment / further detail.

Upvotes: 2

Related Questions