select t.device_id, t.university, COUNT(t.question_id) question_cnt, SUM(t.right_question_cnt) right_question_cnt FROM ( select t1.device_id, t1.university, t2.question_id, case WHEN result = 'right' THEN 1 ...