PostgreSQL Query

Question

I am using a postgresql setup and I have a table something like this:

sec_id     prod_id
1          789
1          908
1          678
13         789
13         123
13         908
15         789

What I want to be able to is determine the overlap (common prod_ids) within the sec_id's. That is, I want to be able output something similar to:

sec_id1    sec_id2    overlap
1          13         2       
1          15         1       
13         1          2       
13         15         1
15         1          1
15         13         1

I don't have very much experience with this and would appreciate any assistance.

Andriy M · Accepted Answer · 2011-11-04 00:19:57Z

2

I am used to MySQL so you may need to adjust the syntax slightly for postgres:

SELECT a.sec_id AS sec_id1, b.sec_id AS sec_id2, COUNT(*) AS overlap
FROM tblname AS a JOIN tblname AS b 
WHERE a.prod_id = b.prod_id AND a.sec_id != b.sec_id 
GROUP BY a.sec_id, b.sec_id

Probably, this would work in PostgreSQL (as well as in MySQL):

SELECT a.sec_id AS sec_id1, b.sec_id AS sec_id2, COUNT(*) AS overlap
FROM tblname AS a JOIN tblname AS b ON a.prod_id = b.prod_id 
WHERE a.sec_id != b.sec_id 
GROUP BY a.sec_id, b.sec_id

edited Nov 4, 2011 at 0:19

Andriy M

78k18 gold badges100 silver badges157 bronze badges

answered Nov 3, 2011 at 20:51

Kevin

56.6k15 gold badges107 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1028475 Over a year ago

Thank you so much. I sincerely appreciate the help.

Collectives™ on Stack Overflow

PostgreSQL Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related