MYSQL - calculating differences within a single table

Question

I am looking for a way to calculate the differences between integers within the same table.

I am planning a MYSQL table that looks like this:

user question answer 1 1 3 1 2 3 1 3 2 1 4 5 1 5 1 2 1 2 2 2 3 2 3 1 2 4 5 2 5 3 3 1 3 3 2 3 3 3 4 3 4 5 3 5 3 4 1 5 4 2 3 4 3 2 4 4 5 4 5 1 

Each user (in this example) answered 5 questions, giving an answer on a scale of 1 to 5.

What I'm looking for is to find out which of users 2, 3 and 4 gave answers that are most similar to those provided by user 1.

What I mean is calculating the difference between the answers given by each user for each question, compared with the answers of user 1, and then adding up these differences.

The user with the smallest number after this addition will be most similar to user 1.

I'm sorry to say that I don’t know where to start building a query that does this efficiently, and wondered if anyone could point me in the right direction? I am also open to any suggestions for a better or more logical way of creating the same results.

Answer 1

 SELECT SUM(ABS(t2.answer - t1.answer)) AS total_diff, t2.user FROM my_table AS t1 LEFT JOIN my_table AS t2 USING(question) WHERE t1.user = 1 AND t2.user != t1.user GROUP BY t2.user ORDER BY total_diff ASC 

result:

 total_diff user 2 4 4 2 4 3 

Answer 2

 SELECT yt1.user, SUM(CASE WHEN yt1.answer = yt2.answer THEN 1 ELSE 0 END) AS howMuchAnswersInCommon FROM yourTable yt1 INNER JOIN yourTable yt2 ON yt1.question = yt2.question WHERE yt2.user = 1 AND yt1.user != 1 GROUP BY yt1.user ORDER BY howMuchAnswersInCommon DESC ; 

This will give you the one with the most common answers to user 1 on top.

Test data:

 /* create table yourTable (user int, question int, answer int); insert into yourTable values (1, 1, 3), (1, 2, 3), (1, 3, 2), (1, 4, 5), (1, 5, 1), (2, 1, 2), (2, 2, 3), (2, 3, 1), (2, 4, 5), (2, 5, 3), (3, 1, 3), (3, 2, 3), (3, 3, 4), (3, 4, 5), (3, 5, 3), (4, 1, 5), (4, 2, 3), (4, 3, 2), (4, 4, 5), (4, 5, 1); */ 

CONCLUSION:

 user howMuchAnswersInCommon 4 4 3 3 2 2