Skip to content
Advertisement

MySQL syntax for summing several count data

I have a MySQL table called things_they_own with two columns: person and thing and my primary key is the combination of the two. Thus, there are several rows where the first column is a certain person with the thing column being different for each, and several rows where the second column is a certain thing and the first column different for each (i.e., each person can own several things and each thing can be owned by several people).

What I want to do: find how many things an inputted person shares with every other person, using just one MySQL query.

My current solution: I’m coding in PHP. I’ve created an array called $things_person_owns which has all the things that an inputted person owns, and an array called ‘$things_in_common_count’ which has all the persons as keys and all the values are initially set as 0. Then, I do this:

foreach ($things_person_owns as $a_thing) {
    $query = "select person from things_they_own where thing = $a_thing";
    $result = $db->query($query);
    while ($theresult = mysqli_fetch_row($result)) {
        $person = $theresult[0];
        $things_in_common_count[$person] += 1;
    }
}

The problem: PHP sends one MySQL query for every thing that the inputted person owns, and each query takes like 0.1s to execute, so it is very slow.

For that reason, I want to find a MySQL-alone solution for my problem, so I could execute only 1 query.

Thank you so much in advance and sorry for any bad wording!

Advertisement

Answer

Based on your updated question, I’ve modified my answer.

First, here’s a list of every person in the table and a count of the things they share in common with a given person (excluding the person being considered).

SELECT tto.person, COUNT(*) AS same_things
FROM things_they_own tto
INNER JOIN things_they_own tto2 ON tto.person <> tto2.person AND tto.thing = tto2.thing
WHERE tto2.person = 'Person A'
GROUP BY tto.person;

Working example.

As a bonus, here’s a list of every person and thing, together with a comma separated list of each other person that owns the same thing.

SELECT tto.person, tto.thing, GROUP_CONCAT(tto2.person ORDER BY tto2.person SEPARATOR ', ') AS also_owned_by
FROM things_they_own tto
INNER JOIN things_they_own tto2 ON tto.person <> tto2.person AND tto.thing = tto2.thing
GROUP BY tto.person, tto.thing
ORDER BY tto.person, tto.thing

If you want to limit the result to a single person, just add a WHERE clause.

WHERE tto.person = 'Person A'

Working example.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement