select b.device_id,b.university,count(b.question_id) as question_cnt, sum(case when b.result = 'right'then 1 else 0 end) as right_question_cnt from ( select * from( select u.device_id,u.university,qpd.question_id,qpd.date,qpd.result from user_profile u left join question_practice_detail ...