To answer your question I had to outline an example. Create a table:
CREATE TABLE Test ( `loginStudent` varchar(10), -- Логин студента `questionNumber` int, -- Номер вопроса `ratingTeacher` bit, -- оценка преподавателя `ratingStudent` bit); -- оценка студента
Fill the table with values:
INSERT INTO Test ( `loginStudent`, `questionNumber`, `ratingTeacher`, `ratingStudent`) VALUES ('denis', 1, 1, 1), ('denis', 2, 1, 1), ('denis', 3, 0, 1), ('denis', 4, 1, 0), ('denis', 4, 0, 1), ('mark', 1, 1, 1), ('mark', 2, 1, 1), ('mark', 3, 0, 0), ('mark', 4, 0, 1), ('mark', 4, 1, 0), ('ann', 1, 0, 1), ('ann', 1, 1, 0), ('ann', 1, 0, 1), ('ann', 2, 1, 0), ('ann', 3, 1, 0), ('ann', 4, 0, 1);
Of course not 50K, but this is enough to see the result. We write a SELECT query to select the required records:
SELECT loginStudent, count(*) as 'Количество не совпавших' FROM Test WHERE ratingTeacher <> ratingStudent -- у кого не совпала оценка GROUP BY loginStudent -- группируем по студенту HAVING count(*) > 0 -- у кого кол-во не совпавших больше 0 -- (можно менять эту величину или не писать вовсе, для Вашего вопроса можно не писать) order by count(*) desc -- сортируем по убыванию. Первая строка - больше всех не совпала
We see the corresponding result, who worse than all coped with the task in general:
The ann student was wrong most of all, six times, this is a general result.
Let's write a slightly different query, in which we will see which questions answer the worst of all and who.
SELECT loginStudent, questionNumber, count(ratingTeacher <> ratingStudent) as 'Количество не совпавших' FROM Test WHERE ratingTeacher <> ratingStudent -- у кого не совпала оценка GROUP BY -- группируем loginStudent, -- по студенту questionNumber -- вопросу HAVING count(ratingTeacher <> ratingStudent) > 0 -- у кого кол-во не совпавших больше 0 order by count(ratingTeacher <> ratingStudent) desc, questionNumber, loginStudent
We look at the result, who did the worst with a specific question:
On this result, it is clear that student Ann was mistaken three times in the first question, and mark with denis were wrong in the fourth two times, and of individual errors.
Since we need everything at once, we use aliases and a subquery. Let's write this query, which will add additional information to our second query:
SELECT r.loginStudent, r.questionNumber, count(r.ratingTeacher <> r.ratingStudent) as 'Количество не совпавших', (select count(*) from Test t where t.loginStudent = r.loginStudent and t.ratingTeacher <> t.ratingStudent) as 'Всего ошибок' -- подзапрос -- в подзапросе нужны Alias-ы, так как таблицы совпадают и имена столбцов одинаковые FROM Test r -- r - псевдоним для данной таблицы (Alias) WHERE r.ratingTeacher <> r.ratingStudent -- у кого не совпала оценка GROUP BY -- группируем r.loginStudent, -- по студенту r.questionNumber -- по вопросу HAVING count(r.ratingTeacher <> r.ratingStudent) > 0 -- у кого кол-во не совпавших больше 0 order by count(r.ratingTeacher <> r.ratingStudent) desc, -- сортируем по убыванию. Первая строка - больше всех не совпала r.questionNumber, r.loginStudent
And here we already see a slightly different table of results, in which we see how many errors a specific question has and how many errors a student has:
The number of not matched - this is our number of errors in the question. That is, how many errors in a particular issue for a particular student. Such a table immediately says who and how many times made a mistake in a particular question, who made the most mistakes and the general statistics of errors in the last column.
