select distinct B.device_id,B.university,COUNT(B.result) as question_cnt, sum(B.right) AS right_question_cnt from (select A.device_id,A.university,Q.question_id,Q.result, case when Q.result = 'right' then 1 else 0 end as 'right' from (select device_id,university from use...