Count common rows between two tables in mysql

Question

so here's my question...

Hi have two tables in mysql, called go_H and go_J, both looking like this:

go_H

+---------------+------------+
| gene          | GoCode     |
+---------------+------------+
| DNAJC25-GNG10 | GO:0004871 |
| DNAJC25-GNG10 | GO:0005834 |
| DNAJC25-GNG10 | GO:0007186 |
| LOC100509620  | GO:0005215 |
| LOC100509620  | GO:0006810 |
| LOC100509620  | GO:0016021 |
| PPIAL4E       | GO:0000413 |
| PPIAL4E       | GO:0003755 |
| PPIAL4E       | GO:0005737 |
| PPIAL4E       | GO:0006457 |
| LOC105371242  | GO:0000413 |
+----------------------------+

go_J

+------------+
| GoCode     |
+------------+
| GO:0007254 |
| GO:0007256 |
| GO:0007257 |
| GO:0042655 |
| GO:0043506 |
| GO:0043507 |
| GO:0043508 |
| GO:0046328 |
| GO:0046329 |
| GO:0046330 |
+------------+

Basically what I want to achieve is to see what GoCode values from go_J appear in GoCode from Go_H, and count them, so as I get a total number o GO ids that are present in both tables.

I have come to select go_H.GoCode and go_J.GoCode, but I don't know how to compare them to find common rows and then count them...

Any help?

Learn what is `JOIN` in mysql – u_mulder Dec 07 '16 at 16:59 — u_mulder, Dec 07 '16 at 16:59

score 3 · Answer 1 · edited May 23 '17 at 11:45

3

SELECT COUNT(*) FROM go_H
INNER JOIN go_J USING GoCode

INNER JOIN => Rows that are in both tables based on the join column (GoCode)

Alternative:

SELECT COUNT(*) FROM go_H h
INNER JOIN go_J ON j.GoCode = h.GoCode

Check this answer out to learn about joins:

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

edited May 23 '17 at 11:45

Community

1
1

answered Dec 07 '16 at 16:59

Arnolio

502
3
8

score 1 · Accepted Answer · answered Dec 07 '16 at 17:00

1

Hope this helps.

select count(*) from go_J j join  go_H h on h.GoCode=j.GoCode;

answered Dec 07 '16 at 17:00

Faizan Younus

793
1
8
13

score 1 · Answer 3 · answered Dec 07 '16 at 17:04

To find how many rows are similar between 2 table

SELECT  COUNT(*) totalCount
FROM    go_H a 
        INNER JOIN go_J b
            ON a.GoCode = b.GoCode

To find how many rows from go_H are not in go_J

SELECT  COUNT(*) totalCount
FROM    go_H a 
        LEFT JOIN go_J b
            ON a.GoCode = b.GoCode
WHERE   b.GoCode IS NULL

To find how many rows from go_J are not in go_H

SELECT  COUNT(*) totalCount
FROM    go_J a 
        LEFT JOIN go_H b
            ON a.GoCode = b.GoCode 
WHERE   b.GoCode IS NULL

score 0 · Answer 4 · answered Dec 07 '16 at 17:08

You can achieve this just in SQL by running a query similar to this:

SELECT
 *,
count (GoCode)
 FROM (    
SELECT GoCode FROM go_H
    UNION
    SELECT GoCode FROM go_H )a
group by a.gocode

This will provide you a table with each code in a column and then the amount of times it is present across both tables

An alternative with PHP would be get both tables into an array by using PDO and use in_array to check

<?php
foreach ($go_H as $GoCode) {
    if (in_array($GoCode, $go_J)) {
        // handle codes in both tables
    }
}

This is not the most efficient method but it will yeild results.

Count common rows between two tables in mysql

4 Answers4