percent match within the same table

Question

Below I have an MySQL database where "id" is just an id for each row. "question" shows the id of the question. There are four questions, 11, 12, 13 and 14. For every question the user has four answer options (1,2,3 and 4) which is stored in the "answer" column. The "user" column indicates what user who answered. In this example we have user 10, 11 and 12

id      question    answer      user
1           11          2       10  
2           12          2       10
3           13          3       10
4           14          4       10
5           11          2       11
6           12          2       11
7           13          4       11
8           14          1       11
9           11          2       12          
10          12          2       12  
11          13          1       12
12          14          1       12

Let's say that user 10 is the reference user which means that I want to know how well user 10 matches with the others. Using SQL and/or php code how can I match the answers of the users such that I get the matches in percent with the highest percent shown first. So in this example I'm looking for something like.

        user     percent    
1       11       50%
2       12       75%

I'm not sure if this is possible all the way with only SQL. Maybe a bit of php is needed to convert the count to % for instance.

Answer 1

Finally i got your desired output
Try this:

SELECT * ,round(count(*)/(SELECT count(*) FROM `list` where user=10)*100) as
 pecentage FROM `list` as l where l.answer=(SELECT m.answer FROM `list` as m
 where m.user=10 and l.question=m.question) group by (l.user) order by pecentage

It will produce out put as

and you can add l.user!=10 in where condition if you don't want user 10 value.