CODEWITHSUNDEEP
mysql
Chetan
Chetan

Reputation: 4995

MySql : Group rows my multiple columns and return the count

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?

Upvotes: 2

Views: 212

Answers (1)

burnall
burnall

Reputation: 842

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

Upvotes: 1

Related Questions