MySql : Group rows my multiple columns and return the count

Question

I have a MySql table having the following structure:

ontology_term
pathway_id
pathway_name

I want to write a query using which we can get mapping between various pathways (having unique id's -> pathway_id) based on the number of common ontology terms.

So the output should be,

Pathway_id1, Pathway_id2, No. of common terms

I know, it can be easily done using a server side language, will it be faster to use MySql instead?

Can you provide some example data that relates to your expected output? — OMG Ponies
– OMG Ponies, Commented Jun 29, 2010 at 16:47

burnall · Accepted Answer · 2010-06-29 10:36:18Z

1

If I understood you right, that is

select a.pathway_id, b.pathway_id, count(*)
from t a
inner join t b on a.ontology_term = b.ontology_term
group by a.pathway_id, b.pathway_id

There is no record if two pathways do not have common terms

answered Jun 29, 2010 at 10:36

burnall

8405 silver badges10 bronze badges

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

1 Comment

Chetan Over a year ago

Thanks, this works. However, I added another condition "AND a.pw_id != b.pw_id" else it will also group each pathway by itself.

Collectives™ on Stack Overflow

MySql : Group rows my multiple columns and return the count

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