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:

JavaScript

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).

JavaScript

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.

JavaScript

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

JavaScript

Working example.

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