percent match within the same table 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.

The Matches of user 10

        user     percent    
1       11       50%
2       12       75%
You need to have a subquery (q1) to count how many answers of every user coincide with those of user 10 (number_of_coinciding_answers). Then, have a second subquery (q2) that counts the number_of_answers. 

Then compute 100.0 * number_of_coinciding_answers / number_of_answers:

Everything put together just looks like:

SELECT
    q1.user, 
    100.0 * q1.number_of_coinciding_answers / q2.number_of_answers AS percent
FROM
    (SELECT
        t.user, 
        count(good.answer) AS number_of_coinciding_answers
    FROM
        t 
        JOIN t AS good ON good.question = t.question 
                      AND good.user = 10 
                      AND good.answer = t.answer
    WHERE
        t.user &lt;&gt; 10
    GROUP BY
        t.user
    ) AS q1
    CROSS JOIN
    (SELECT
        count(*) AS number_of_answers
    FROM
        t
    WHERE 
        t.user = 10
    ) AS q2  
ORDER BY
    q1.user ;


You'll get


user |  percent
---: | -------:
  11 | 50.00000
  12 | 50.00000


Note that the JOIN condition is taking care of just finding matching answers between the current user and the reference user (user 10).

Note also that the numbers retrieived are 50% and 50%, which are the correct ones, as pointed out by Rick James.



You can write it in a slightly more compact fashion (that MySQL actually seems to handle better) by using:

SELECT
    q1.user, 
    100.0 * q1.number_of_coinciding_answers / 
          (SELECT count(*) FROM  t WHERE  t.user = 10) AS percent
FROM
    (SELECT
        t.user, 
        count(good.answer) AS number_of_coinciding_answers
    FROM
        t 
        JOIN t AS good ON good.question = t.question 
                      AND good.user = 10 
                      AND good.answer = t.answer
    WHERE
        t.user &lt;&gt; 10
    GROUP BY
        t.user
    ) AS q1
ORDER BY
    q1.user ;




All these queries are helped by having a multi-column covering index on columns (user, question, answer). That is, in MySQL, you'd define your table 

CREATE INDEX idx_t_uqa 
    ON t (user, question, answer) ;


You can see a more step-by-step approach and all the settings at dbfiddle here