CODEWITHSUNDEEP
sqlsqlite
DeeeeRoy
DeeeeRoy

Reputation: 487

How to count the number of times a pair occurs across columns SQL

I want to write a SQL query to do the following. I have the following table, lets call it test_table:

id1, id2, link_group
A, B, 1
A, B, 17
A, B, 12
A, C, 1
B, C, 1
D, E, 2
D, E, 5

I want to count the number of times pairs of columns id1, and id2 appear. The result table would look like this. Explicitly A, and B occurred together in three separate link_group categories, A,C and B,C in one link_group, and D, E in 2 link_groups.

id1, id2, coappearances
A, B, 3
A, C, 1
B, C, 1
D, E, 2

Upvotes: 0

Views: 584

Answers (1)

Vishnu Kunchur
Vishnu Kunchur

Reputation: 1726

SELECT id1, id2, COUNT(1) AS coappearances
FROM test_table
GROUP BY id1, id2

Upvotes: 2

Related Questions