I'm sure there are many ways to do this. Here's one:
CREATE TABLE QA (id int, question int, answer int, user int);
INSERT INTO QA
VALUES (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)
;
SET @QCount := (SELECT COUNT(*) as QCount from QA WHERE QA.user = 10);
SET @QCount := IF(@QCount = 0, NULL, @QCount);
SELECT q1.user, ROUND(COUNT(a1.answer) * 100.0 / @QCount, 1) as percent
FROM QA q1
LEFT JOIN QA a1 ON ( q1.question = a1.question
AND q1.answer = a1.answer
AND a1.user = 10
)
WHERE q1.user <> 10
GROUP BY q1.user
;
Tested via this db-fiddle
Here's how it works:
I am assuming that (in this case, at least) user 10
's answers should be considered to be the answer key. We select the number of questions for that user to get the total question count.
Then, for each other user who has at least one answer, we get all the answers for each user, and LEFT JOIN
them to the answer key questions and answers. If the answer key answer and the user's answer don't match, then the left join fails to find a match.
So, for all the right answers, we have a matched row from the answer key; for the wrong answers, we have the user's answer row, with no matched row.
We then count the number of matched rows for each user. The COUNT
function doe snot count values that are NULL; by counting on a column from the LEFT JOIN
table (the answer key), we'll only get a count of the right answers. NOTE: if, for some reason, there's no answer for some questions for a given those won't exists and won't be counted; so, they'll be treated as wrong answers.
To get the percentage of right answers, we simply multiply the number of wrong answers by 100, and divide by the total number of questions in the answer key. I leave the formatting aspect of showing an actual percent symbol up to the user, if required.
Note that the actual results are 50% for users 11 and 12; you showed 75% for one, bit the answers in your sample data show only two right for each user.
Best Answer
If the server naming format/convention is fixed then you can get desired output using conditional aggregation
Output:
Here is SQLFiddle demo