CODEWITHSUNDEEP
sqlpostgresqlgroupingcombinatorics
Renato Dinhani
Renato Dinhani

Reputation: 36746

How to count the relations between names in the same table?

I have a table called names with rows consisting of a name and a group for that name.

I want to count how much times any two names appear together in any group.

Table

name    | group
--------+------
Renato  |   1
John    |   1 
Paul    |   1
Renato  |   2
John    |   2
John    |   3
Paul    |   3

Expected result

name 1  | name 2 |count
--------+--------+-----
Renato  | John   |  2
Renato  | Paul   |  1
John    | Renato |  2 
John    | Paul   |  2
Paul    | Renato |  1
Paul    | John   |  2

How I can do this?

Upvotes: 0

Views: 108

Answers (2)

BAK
BAK

Reputation: 1015

SELECT n1.name AS "name 1", n2.name AS "name 2",
    COUNT(*) AS "count"
FROM "names" AS n1
JOIN "names" AS n2
    ON n1.group = n2.group
WHERE n1.name <> n2.name
GROUP BY n1.name, n2.name

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65314

SELECT 
  lefttbl.name as name_1,
  righttbl.name as name_2,
  count(*) as together_count
FROM
  xx AS lefttbl
  INNER JOIN xx AS righttbl on lefttbl.group=righttbl.group
WHERE lefttbl.name<>righttbl.name
GROUP BY name_1,name_2
;

Gives you the desired structure. I updated the SQL to remove the need for quotes, as to satisfy those, who are able to correctly identify a quoting incompatibility, but unwilling to correct it.

Upvotes: 1

Related Questions