select device_id,university,sum(nquestion)question_cnt,sum(nright_question)right_question_cnt from (select device_id,university,if(question_id is not null,1,0) nquestion,if(result='right',1,0) nright_question from(select u.device_id,university,question_id,result,date from user_profile u left join (s...